Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
can i suggest this
sum( aggr( above( sum( {$<MonthYear>} [Claims PMPM (1M0)]),1,12 ),Country_PMPM,MonthYear))
Thats formula of rolling... and same issue in this....
May be the best way to handle this is to use The As-Of Table
can you paste code as not able to open it due to lic...
For you
Script
Table:
LOAD Date(MonthStart(Date#(MonthYear, 'MMM-YY')), 'MMM-YY') as MonthYear,
Claims
INLINE [
MonthYear, Claims
Jul-17, 3
Jun-17, 130
May-17, 316
Apr-17, 225
Mar-17, 172
Feb-17, 247
Jan-17, 24
Dec-16, 3
Nov-16, 22
Oct-16, 53
Sep-16, 25
Aug-16, 11
May-16, 26
Apr-16, 16
Mar-16, 31
Feb-16, 101
Jan-16, 5
Dec-15, 22
Sep-15, 18
];
AsOfTable:
LOAD DISTINCT MonthYear as AsOfMonthYear,
MonthYear,
'CY' as Flag
Resident Table;
Concatenate (AsOfTable)
LOAD DISTINCT MonthYear as AsOfMonthYear,
Date(AddYears(MonthYear, -1), 'MMM-YY') as MonthYear,
'PY' as Flag
Resident Table;
Dimension
AsOfMonthYear
Expressions
=Sum({<Flag = {'CY'}>}Claims)
=Sum({<Flag = {'PY'}>}Claims)
Added DISTINCT to AsOfTable which was not there in the original qvw posted to make this more efficient
is there any other way of doing it, as i have very big dashboard with lot of data and i can not add this set analysis of CY and PY in every expression...
This is the best and most efficient way to do what you are trying to do (even for a big database)... any alternative will drastically slow down your application
Hi Ankit,
Maybe this:
Claims:
Load
Date#(MonthYear,'MMM-YYYY') as MonthYear,
Claims;
LOAD * INLINE [
MonthYear, _MonthYear, Claims
Jul-2017, 23, 3
Jun-2017, 22, 130
May-2017, 21, 316
Apr-2017, 20, 225
Mar-2017, 19, 172
Feb-2017, 18, 247
Jan-2017, 17, 24
Dec-2016, 16, 3
Nov-2016, 15, 22
Oct-2016, 14, 53
Sep-2016, 13, 25
Aug-2016, 12, 11
May-2016, 9, 26
Apr-2016, 8, 16
Mar-2016, 7, 31
Feb-2016, 6, 101
Jan-2016, 5, 5
Dec-2015, 4, 22
Sep-2015, 1, 18
];
Left Join(Claims)
LOAD
AddMonths(MonthYear,12) as MonthYear,
Claims as [Last Year Claims]
Resident Claims;
To get this:
MonthYear | Claims | Last Year Claims |
---|---|---|
Jul-2017 | 3 | |
Jun-2017 | 130 | |
May-2017 | 316 | 26 |
Apr-2017 | 225 | 16 |
Mar-2017 | 172 | 31 |
Feb-2017 | 247 | 101 |
Jan-2017 | 24 | 5 |
Dec-2016 | 3 | 22 |
Nov-2016 | 22 | |
Oct-2016 | 53 | |
Sep-2016 | 25 | 18 |
Aug-2016 | 11 | |
May-2016 | 26 | |
Apr-2016 | 16 | |
Mar-2016 | 31 | |
Feb-2016 | 101 | |
Jan-2016 | 5 | |
Dec-2015 | 22 | |
Sep-2015 | 18 |
Regards
Andrew