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

[#1]

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?

[sunny_talwar]

Maybe this

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

or this

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

[poppypearce]

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

[sunny_talwar]

Try this in that case

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

[poppypearce]

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.

[sunny_talwar]

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

[poppypearce]

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]

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.

[sunny_talwar]

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

[poppypearce]

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)