Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
UserID2626
Partner - Creator III
Partner - Creator III

Dynamic MOM Qlik sense

Hi,

Help to me find MOM Dynamically as per below table, condition is in last column

Month yearrevenueMOM 
Jan-1925645-25645Jan2019 Vs Dec 2019
Feb-19545-25100feb2019 Vs jan2019
Mar-1974826937March2019 Vs feb2019
Apr-19654-6828 
May-19547-107 

 

Thanks

Labels (7)
1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

If the fields are sorted properly on table, same expression will work perfectly.

Untitled2.png

But if you make it work on unsorted table, you should use aggr function with structure parameters to sort fields properly in background for calculations.

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm

 

sum(aggr(sum(Revenue)-alt(above(sum(Revenue)),0),(Type, (TEXT, Ascending)),(MonthYear, (NUMERIC, Ascending))))

 

 

Pic 1 : In Sorted table (first type,second monthyear) both expression generates the same and true result.

Untitled.png

Pic 2 : In unsorted table (first monthyear) both expression generates different result. Only expression with aggr one works correctly.

Untitled1.png

Hope it helps...

View solution in original post

3 Replies
kaanerisen
Creator III
Creator III

Hi,

sum(Revenue)-alt(above(sum(Revenue)),0)

Untitled.png 

UserID2626
Partner - Creator III
Partner - Creator III
Author

Thanks @kaanerisen 

 

It looks so simple, But when i include dimension i have to get MOM as per the dimension.

 

Month yearTyperevenueMOM 
Jan-19A25645-25645A of Jan2019 Vs A of Dec 2019
Feb-19B545-1B Of feb2019 Vs B of jan2019
Mar-19C7454C of March2019 Vs C of feb2019
Apr-19D654400 
May-19E547  
Jan-19B546  
Feb-19C20  
Mar-19D254  
Apr-19A54  
May-19E12  
kaanerisen
Creator III
Creator III

If the fields are sorted properly on table, same expression will work perfectly.

Untitled2.png

But if you make it work on unsorted table, you should use aggr function with structure parameters to sort fields properly in background for calculations.

https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm

 

sum(aggr(sum(Revenue)-alt(above(sum(Revenue)),0),(Type, (TEXT, Ascending)),(MonthYear, (NUMERIC, Ascending))))

 

 

Pic 1 : In Sorted table (first type,second monthyear) both expression generates the same and true result.

Untitled.png

Pic 2 : In unsorted table (first monthyear) both expression generates different result. Only expression with aggr one works correctly.

Untitled1.png

Hope it helps...