Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Above() not working to obtain previous data from pivot table with 2 dimensions

Hi,

I have a set of data where I need to obtain the previous record from the pivot table. The pivot table contains 2 dimensions ("=YEAR(MonthYear)" -->YEAR and "=MONTH(MonthYear)" -->MONTH) which is created by this field called MonthYear. To obtain the previous month's data, I use this expression: "=above(count([Complaint ID]))". Im unable to obtain the previous data for all Jan rows. Can anyone help?

Picture1.png

10 Replies

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

Maybe this

=Above(TOTAL Count([Complaint ID]))

or this

=Aggr(Above(Count([Complaint ID])), MonthYear)

poppypearce
Contributor

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

Hi Sunny,

Tried Both the options but Not getting the Data for Dec.

Also above function fails when Single month is selected. could you please suggest some other way to get previous month data to this table.

Regards,

Poppy

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

Try this in that case

=Aggr(Above(Count({<MonthYear, Year, Month>}[Complaint ID])), MonthYear)

poppypearce
Contributor

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

Hey Sunny your Suggestion is Awesome, it works great, but problem is When i dont have sequence of months in the Fact table for Example i have Data for Jan 2016, Mar 2016, Apr 2016 in this Data for Previous month of Mar 2016 should be '0' as Feb 2016 does not exist, but Since we are using above function here its picking Jan 2016 Data as Previous month Data for Mar 2016.

Could you please suggest me on this.

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

The best way to handle this is to use The As-Of Table

poppypearce
Contributor

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

Hi Sunny,

When we are using as of tables should we use as of month as a dimension in our table??

because here we need to show both current month and previous month in the same row.

Please suggest...

poppypearce
Contributor

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

Hi Sunny,

I tried using As Of Month Table for the Previous Month i have used the Expression as Sum({$<MonthDiff={1}>}Sales)  but Still i am not getting previous month Data, Could you please suggest me on this.

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

Are you using AsOfMonth as your dimension or not? You need to use it AsOfMonth as your dimension instead of Month field

poppypearce
Contributor

Re: Above() not working to obtain previous data from pivot table with 2 dimensions

After using AsofMonth as dimension its working perfect Sunny Thanks a lot for your Suggestion.

My final table has Asof month as Dimension and expressions for Current Month is Sum({$<MonthDiff={0}>}Sales) and Previous Month is Sum({$<MonthDiff={1}>}Sales)