Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Help with vairable and time dimension

I am using this formula which give me current month at all time:

((Sum(aggr(count({<Month={'$(vMaxMonth)'},MonthYear=>}distinct USER_ID),MonthYear,Application_Version,[Application]))) / Count({<Month={'$(vMaxMonth)'}>} distinct MonthYear))

((Sum(aggr(count({<Month={'$(vPriorMonth)'},MonthYear=>}distinct USER_ID),MonthYear,Application_Version,[Application]))) / Count({<Month={'$(vPriorMonth)'}>} distinct MonthYear))

1 Solution

Accepted Solutions

Re: Help with vairable and time dimension

Change this variable to:

LET vPriorMonthYear = '=date(addmonths(max(MonthYear),-1), ''MMM-YY'')';

And change your expression to:

Sum(aggr(count({<MonthYear={'$(vPriorMonthYear)'}>}distinct USER_ID),[Application]))

8 Replies
Not applicable

Re: Help with vairable and time dimension

juanjosemoralesnicole_kowalsky

Will one of you have a chance to help me with this please!

THANKS!

Re: Help with vairable and time dimension

Change this variable to:

LET vPriorMonthYear = '=date(addmonths(max(MonthYear),-1), ''MMM-YY'')';

And change your expression to:

Sum(aggr(count({<MonthYear={'$(vPriorMonthYear)'}>}distinct USER_ID),[Application]))

xufei123
Valued Contributor

Re: Help with vairable and time dimension

I'll create another MonthStartDate field in the script like this:

Then in the front the current month is:

=sum(aggr(count({<MonthYear={"$(=date(max(MonthYear)))"}, MonthYear=>} distinct USER_ID),MonthYear,Application_Version,[Application]))

The previous month is:

=sum({<MonthYear=, MonthStartDate={"$(=monthstart(max(MonthStartDate),-1))"}>} aggr(count({<MonthYear=, MonthStartDate={"$(=monthstart(max(MonthStartDate),-1))"}>} distinct USER_ID),Application_Version,[Application]))

Not applicable

Re: Help with vairable and time dimension

Worked like a charm! You are amazing!

xufei123
Valued Contributor

Re: Help with vairable and time dimension

If you select Nov-16, Nicole's solution will show 17 for "Previous Month", which is not right. It should be 24. (If you select Oct-16, you'll see current month is 24).

Not applicable

Re: Help with vairable and time dimension

Hey Fei, Thanks for the reply. Your solution fixes the previous month metric- but I am getting 178 for current month.

I was mainly trying to solve for the previous month number for past month and both of yours answers gets me the right result!

Thank you so much!

xufei123
Valued Contributor

Re: Help with vairable and time dimension

Please be aware when you select any MonthYear, you have selected some application and application_version behind the scene:

So when you select Nov-16, there are only D, F and I in the Application_Version are associated and therefore Nicola's solution only shows the numbers for previous month and for D, F, I.

Actually in Oct-16, there is no userId for Application 1 and Application_Version D at all but Nicola's solution shows 2. That is because she aggr the expression by application only.

Hope this can help and most importantly you understand whySmiley Happy

Cheers.

Fei

Re: Help with vairable and time dimension

Thanks for finding that xufei123‌.  I randomly checked a bunch of months, but obviously not November.

Community Browser