Aggr Function

[#1]

Hi All,

Can someone tell me what I'm doing wrong with this function.

=if(Aggr(rank(count(DISTINCT(change_label='expansion')),d1subcust_clientname)))

Thanks in Advance,

[sunny_talwar]

Would it be possible to share a sample?

[#3]

Hi Sunny,

Please find attached the sample data set.

Thanks,

[sunny_talwar · Accepted solution]

I think there are 190 distinct values

[#5]

Thanks for all your help Sunny.

[#6]

Hi Sunny,

This formula you had suggested to me. This formula works well only with the four quarters is there anyway I can make it the Rolling Quarters concept. If the end user clicks on Feb it would give the end user Nov thru Jan, if he/she clicks on Mar the formula would give the end user Dec thru Feb.

Please help!!!!!!!!!!!

=money(Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)

[sunny_talwar]

Look here, I learned something new from Oleg: rolling 3 months not working properly with set analysis...?

[#8]

Sorry Sunny, I don't comprehend the post. Is rolling months possible in this case? I need to make two text boxes, in one I would show the four quarters and in another one text box would show the rolling quarters (e.g.Nov to Jan, Dec to Feb, Feb to April).

=money(Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)) * 4 /

Sum({[Book3]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)

Please help!!!!!!!!!!!

[sunny_talwar]

‌I am traveling right now... But if nobody help you resolve your issue, I am more than happy to take a quick look at your issue