Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Help to me find MOM Dynamically as per below table, condition is in last column
Month year | revenue | MOM | |
Jan-19 | 25645 | -25645 | Jan2019 Vs Dec 2019 |
Feb-19 | 545 | -25100 | feb2019 Vs jan2019 |
Mar-19 | 7482 | 6937 | March2019 Vs feb2019 |
Apr-19 | 654 | -6828 | |
May-19 | 547 | -107 |
Thanks
If the fields are sorted properly on table, same expression will work perfectly.
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.
Pic 2 : In unsorted table (first monthyear) both expression generates different result. Only expression with aggr one works correctly.
Hope it helps...
Hi,
sum(Revenue)-alt(above(sum(Revenue)),0)
Thanks @kaanerisen
It looks so simple, But when i include dimension i have to get MOM as per the dimension.
Month year | Type | revenue | MOM | |
Jan-19 | A | 25645 | -25645 | A of Jan2019 Vs A of Dec 2019 |
Feb-19 | B | 545 | -1 | B Of feb2019 Vs B of jan2019 |
Mar-19 | C | 74 | 54 | C of March2019 Vs C of feb2019 |
Apr-19 | D | 654 | 400 | |
May-19 | E | 547 | ||
Jan-19 | B | 546 | ||
Feb-19 | C | 20 | ||
Mar-19 | D | 254 | ||
Apr-19 | A | 54 | ||
May-19 | E | 12 |
If the fields are sorted properly on table, same expression will work perfectly.
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.
Pic 2 : In unsorted table (first monthyear) both expression generates different result. Only expression with aggr one works correctly.
Hope it helps...