https://community.qlik.com/t5/App-Development/Dimension-is-MonthYear-How-to-count-for-the-previous-month/m-p/2136663#M92377 <P>Hi, Set analysis is applied before calculating the table, so it doen't uses the dimension value.</P> <P>The access the upper row you can use Above() function:&nbsp;<A href="https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecordFunctions/above.htm" target="_blank">https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecordFunctions/above.htm</A></P> <P>Other solutions might be:</P> <P>- Use an as-of calendar</P> <P>- Create additional rows for past months so you can use an expression like Count(...[%_user_id_prev_month]).</P> <P>- Have the data precalculated</P> <P>&nbsp;</P> Fri, 10 Nov 2023 10:44:51 GMT rubenmarin 2023-11-10T10:44:51Z https://community.qlik.com/t5/App-Development/Dimension-is-MonthYear-How-to-count-for-the-previous-month/m-p/2136474#M92355 <P>Hi,</P> <P>I have the following data (just one table):</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="J30Ti" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/119673i22B098423392DE61/image-size/large?v=v2&amp;px=999" role="button" title="J30Ti" alt="J30Ti" /></span></P> <P>And on sheet I want to create a table that will have <CODE>MonthYear</CODE> as dimension and two measures:</P> <UL> <LI><CODE>Current</CODE>: Counts distinct users that had <CODE>Activity = "Payment"</CODE>. Expression for this is: <CODE>Count({&lt;[Activity]={'Payment'}&gt;} distinct [%_user_id])</CODE> and it works fine.</LI> <LI><CODE>Previous</CODE>: Counts distinct users that had <CODE>Activity = "Payment"</CODE> <STRONG>during previous month</STRONG>. For example, if the value of the <CODE>MonthYear</CODE> dimension is <CODE>Feb 2023</CODE>, then it should show count for the <CODE>Jan 2023</CODE>. Or, for <CODE>Jan 2023</CODE> it should show count for <CODE>Dec 2022</CODE> ...</LI> </UL> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="kLs5M" style="width: 977px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/119674i1B4B685D3B0AE6F7/image-size/large?v=v2&amp;px=999" role="button" title="kLs5M" alt="kLs5M" /></span></P> <P>Do you know what the expression for the <CODE>Previous</CODE> measure would be?</P> <P>I tried this:</P> <PRE><CODE>Count({&lt;MonthYear={'$(=Date(AddMonths(MonthYear, -1), 'MMM YYYY'))'}, [Activity]={'Payment'}&gt;} distinct [%_user_id]) </CODE></PRE> <P>but it does not work.</P> <P>Maybe such a measure is not possible in this particular case?</P> Thu, 09 Nov 2023 23:33:52 GMT https://community.qlik.com/t5/App-Development/Dimension-is-MonthYear-How-to-count-for-the-previous-month/m-p/2136474#M92355 RoyBatty 2023-11-09T23:33:52Z https://community.qlik.com/t5/App-Development/Dimension-is-MonthYear-How-to-count-for-the-previous-month/m-p/2136663#M92377 <P>Hi, Set analysis is applied before calculating the table, so it doen't uses the dimension value.</P> <P>The access the upper row you can use Above() function:&nbsp;<A href="https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecordFunctions/above.htm" target="_blank">https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRecordFunctions/above.htm</A></P> <P>Other solutions might be:</P> <P>- Use an as-of calendar</P> <P>- Create additional rows for past months so you can use an expression like Count(...[%_user_id_prev_month]).</P> <P>- Have the data precalculated</P> <P>&nbsp;</P> Fri, 10 Nov 2023 10:44:51 GMT https://community.qlik.com/t5/App-Development/Dimension-is-MonthYear-How-to-count-for-the-previous-month/m-p/2136663#M92377 rubenmarin 2023-11-10T10:44:51Z