topic Re: Previous Year Month Value Issue in QlikView

Previous Year Month Value Issue

Anonymous — Wed, 18 Oct 2017 07:49:42 GMT

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

Re: Previous Year Month Value Issue

pradosh_thakur — Wed, 18 Oct 2017 09:01:54 GMT

can i suggest this

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

Re: Previous Year Month Value Issue

Anonymous — Wed, 18 Oct 2017 09:28:15 GMT

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

Re: Previous Year Month Value Issue

sunny_talwar — Wed, 18 Oct 2017 12:04:52 GMT

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

Re: Previous Year Month Value Issue

Anonymous — Wed, 18 Oct 2017 12:34:11 GMT

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

Re: Previous Year Month Value Issue

Anil_Babu_Samineni — Wed, 18 Oct 2017 12:40:01 GMT

For you

Re: Previous Year Month Value Issue

sunny_talwar — Wed, 18 Oct 2017 12:40:14 GMT

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

Re: Previous Year Month Value Issue

Anonymous — Wed, 18 Oct 2017 13:13:56 GMT

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

Re: Previous Year Month Value Issue

sunny_talwar — Wed, 18 Oct 2017 13:28:01 GMT

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

Re: Previous Year Month Value Issue

effinty2112 — Wed, 18 Oct 2017 14:55:31 GMT

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

Re: Previous Year Month Value Issue

sunny_talwar — Wed, 18 Oct 2017 15:00:53 GMT

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

Re: Previous Year Month Value Issue

effinty2112 — Wed, 18 Oct 2017 15:18:26 GMT

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

Re: Previous Year Month Value Issue

sunny_talwar — Wed, 18 Oct 2017 15:23:25 GMT

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

Re: Previous Year Month Value Issue

madhumitha — Wed, 18 Oct 2017 15:50:38 GMT

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!

Re: Previous Year Month Value Issue

effinty2112 — Wed, 18 Oct 2017 16:11:56 GMT

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