topic Re: Help with vairable and time dimension in App Development

Help with vairable and time dimension

Mon, 07 Nov 2016 17:39:16 GMT

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

Re: Help with vairable and time dimension

Mon, 07 Nov 2016 20:47:20 GMT

juanjosemorales‌ nicole_kowalsky‌

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

THANKS!

Re: Help with vairable and time dimension

Nicole-Smith — Mon, 07 Nov 2016 21:40:45 GMT

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

Re: Help with vairable and time dimension

NZFei — Tue, 08 Nov 2016 03:18:55 GMT

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

Re: Help with vairable and time dimension

Tue, 08 Nov 2016 16:03:05 GMT

Worked like a charm! You are amazing!

Re: Help with vairable and time dimension

NZFei — Tue, 08 Nov 2016 19:53:50 GMT

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

Re: Help with vairable and time dimension

Tue, 08 Nov 2016 20:04:40 GMT

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!

Re: Help with vairable and time dimension

NZFei — Tue, 08 Nov 2016 20:22:19 GMT

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

Re: Help with vairable and time dimension

Nicole-Smith — Tue, 08 Nov 2016 20:51:39 GMT

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