Top 10 Variance Amount

[Syahfira1]

Hello,

How I can create  set analysis to get the top 10 Variance amount which is subtraction of current amount withh previous amount?

This is my formula to get the variance amount between current and previous month.

sum({<[Reporting Date.MasterCalendar.Date] = {"$(=vLatestDate)"}>}"Past Due")
-
sum({<[Reporting Date.MasterCalendar.Date]={"$(=date(addmonths(vLatestDate,-1,1)))"}>}"Past Due")

This is my formula to find the top 10 variance by Country

sum({$<[Country]={"=rank(sum({<[Reporting Date.MasterCalendar.Date] = {"$(=vLatestDate)"}>}"Past Due")
-
sum({<[Reporting Date.MasterCalendar.Date]={"$(=date(addmonths(vLatestDate,-1,1)))"}>}"Past Due"),4,1) <= $(vTop)"}>} [Invoice Amount USD])

But my top 10 formula is not working when I put the inner set analysis. How I can put subtraction calculation in nested set analysis?

Help is much appreciated.

[ajaykakkar93]

hi,
below is the sample code

rank(

sum({<[Reporting Date.MasterCalendar.Date] = {"$(=vLatestDate)"}>}"Past Due")
-
sum({<[Reporting Date.MasterCalendar.Date]={"$(=date(addmonths(vLatestDate,-1,1)))"}>}"Past Due")

,4)

[Syahfira1]

Hi Ajay, 

May I know what I should put for the Step 3?

Should I replace it with subtraction set analysis?

But, it didnt work.

[Syahfira1]

@sunny_talwar  can you help me on this please? would appreciate it so much 🌻

[ajaykakkar93]

rank(

sum({<[Reporting Date.MasterCalendar.Date] = {"$(=vLatestDate)"}>}"Past Due")
-
sum({<[Reporting Date.MasterCalendar.Date]={"$(=date(addmonths(vLatestDate,-1,1)))"}>}"Past Due")

,4)


just add it in separate measure & look at the rank if it is coming as you needed accordingly