Previous Year Month Value Issue

[Anonymous]

HI All,

                  I am facing issue in calculating last year month value in table chart as

i have claims data in which i have to compare current month data with same last year month table..

i need to create table chart for same. but in some selections few between months are missing like eg -

JUl-2017 claims are 3 and Jul 2016 does not exist so value should be zero

same as May-2017 its 216 and last year value should come 26

i tried to use --

sum( aggr( above( sum( {$<MonthYear>}  [Claims PMPM (1M0)]),12 ),Country_PMPM,MonthYear))

but due to missing month not getting correct answer...

MonthYear _MonthYear Claims Last Year Claims

Jul-2017 23 3 26

Jun-2017 22 130 16

May-2017 21 316 31

Apr-2017 20 225 101

Mar-2017 19 172 5

Feb-2017 18 247 22

Jan-2017 17 24 18

Dec-2016 16 3 0

Nov-2016 15 22 0

Oct-2016 14 53 0

Sep-2016 13 25 0

Aug-2016 12 11 0

May-2016 9 26 0

Apr-2016 8 16 0

Mar-2016 7 31 0

Feb-2016 6 101 0

Jan-2016 5 5 0

Dec-2015 4 22 0

Sep-2015 1 18 0

please see attach image

thanks,

Ankit

[sunny_talwar]

Absolutely do not recommend doing a left join as the fact table might have multiple MonthYear and you will be doing a Cartesian product here

[effinty2112]

Hi Sunny,

I had made the assumption, perhaps wrongly, that the data we're looking at had already been aggregated on the MonthYear so the values in this column would be unique.

Cheers

Andrew

[sunny_talwar]

If the OP is concerned about using AsOfTable, then Joins are going to kill the application (I think). What is your take on this?

[madhumitha]

Hi Ankit,

The only way out be to create a table which would have distinct of Year month or generate a data set which would have a full year month combination. you need to outer join this table and you claims table defaulting the claims amount to be zero.

Thanks!

[effinty2112]

Hi Sunny,

The more I think about it the more I think that your approach is preferable. Even if the data is aggregated the way I assumed I think there's less that can go wrong with the AsOfTable idea if the data model develops over time.

Cheers

Andrew