Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Previous Year Month Value Issue

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

14 Replies
pradosh_thakur
Master II
Master II

can i suggest this

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

Learning never stops.
Anonymous
Not applicable
Author

Thats formula of rolling... and same issue in this....

sunny_talwar

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

Capture.PNG

Anonymous
Not applicable
Author

can you paste code as not able to open it due to lic...

Anil_Babu_Samineni

For you

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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

Anonymous
Not applicable
Author

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...

sunny_talwar

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

effinty2112
Master
Master

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-20173 
Jun-2017130 
May-201731626
Apr-201722516
Mar-201717231
Feb-2017247101
Jan-2017245
Dec-2016322
Nov-201622 
Oct-201653 
Sep-20162518
Aug-201611 
May-201626 
Apr-201616 
Mar-201631 
Feb-2016101 
Jan-20165 
Dec-201522 
Sep-201518

Regards

Andrew