Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

UserID2687
New Contributor 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

1 Solution

Accepted Solutions
Highlighted
kaanerisen
Contributor III

Re: Dynamic MOM Qlik sense

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

3 Replies
kaanerisen
Contributor III

Re: Dynamic MOM Qlik sense

Hi,

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

Untitled.png 

UserID2687
New Contributor III

Re: Dynamic MOM Qlik sense

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  
Highlighted
kaanerisen
Contributor III

Re: Dynamic MOM Qlik sense

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