Help with vairable and time dimension

[#1]

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))

[#2]

juanjosemorales‌ nicole_kowalsky‌

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

THANKS!

[Nicole-Smith · Accepted solution]

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]))

[NZFei]

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]))

[#5]

Worked like a charm! You are amazing!

[NZFei]

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).

[#7]

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!

[NZFei]

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 why:)

Cheers.

Fei

[Nicole-Smith]

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