8 Replies Latest reply: Nov 8, 2016 3:51 PM by Nicole Smith

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

• ###### Re: Help with vairable and time dimension

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:

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

• ###### Re: Help with vairable and time dimension

Worked like a charm! You are amazing!

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

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

• ###### 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!

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

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.