topic Re: Need to get Last year data in QlikView

Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 13:34:44 GMT

Hi All,

I have fields like cat , sales org and date field where i have Years from 2016 Jan to 2017 Dec.

And i have a field called cons where there are values of 2016 jan and 2017 dec.

Now i need to add one more field called Cons_LY where if i select jan 2017 it should show Jan 2016 data and similarly if i select 2017 feb it should show for feb 2017 data..

Can you please help me how can i do it.. Can it be done in script

Attached app for ref.

Thanks,

Bharat

Re: Need to get Last year data

Anil_Babu_Samineni — Thu, 15 Feb 2018 13:49:36 GMT

May be add this in script?

Left Join(T1)

LOAD If(calenday_year_code=2016,Sum(Consumption)) as Field Resident T1 Where Year = '2017';

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 13:56:10 GMT

Hi Anil,

Thanks.. But i need to use field bec it has yearmonth combination.

And i tried your expression but getting error. Below code for ref:

T1:

LOAD Sales_Organization,

Cat,

Calendar_Month_Code,

Calendar_Year_Code,

Consumption

FROM

(qvd) where Calendar_Year_Code>='2016' ;

left join(T1)

LOAD

If(Calendar_Year_Code=2016,Sum(Consumption)) as Cons_LY

Resident T1

Where Calendar_Year_Code = '2017';

Re: Need to get Last year data

Anil_Babu_Samineni — Thu, 15 Feb 2018 14:01:34 GMT

My bad,

T1:

LOAD Sales_Organization, Cat, Calendar_Month_Code, Calendar_Year_Code, Consumption

FROM (qvd) where Calendar_Year_Code>='2016' ;

left join(T1)

LOAD Calendar_Month_Code, Calendar_Year_Code, If(Calendar_Year_Code=2016,Sum(Consumption)) as Cons_LY

Resident T1

Where Calendar_Year_Code = '2017' Group By Calendar_Month_Code, Calendar_Year_Code;

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 14:21:49 GMT

Sorry it is not working.

please find below requirement:

I have CalendarMonthCode Cons ConslY(i need to derive this field)

201601 100 0(bec no data for 201501)

201602 200 0

201701 300 100

201702 300 200

Hope i am clear and please let me know if you need anything more and CalendarMonthCode is the field i need to use.

Please let me know if you need anything more

Re: Need to get Last year data

rubenmarin — Thu, 15 Feb 2018 14:40:33 GMT

Hi Bharat, one easy way can be load the data as LYConsumption, adding one year to the data:

Concatenate (T1)

LOAD Sales_Organization,

Cat,

Calendar_Month_Code,

Calendar_Year_Code+1 as Calendar_Year_Code,

Consumption as LYConsumption

Resident T1;

A where condition can be added to not generate data from current year (wich will be loaded as 2019 data).

Another options of acces data of different dates can be using an as-of calendar: The As-Of Table

Re: Need to get Last year data

sunny_talwar — Thu, 15 Feb 2018 14:40:38 GMT

Can you share T1.qvd?

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 14:42:36 GMT

Hi Sunny,

PFA qvd..

Please let me know if you need anything more.

Calendar year i have given >2016 because i have years from 1981 that is why i have given like that..

Thanks..

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 14:45:44 GMT

Hi Ruben,

Thanks but i need use CalendarMonthCode instead of Calendar_Year_Code because i needed in MonthLevel basis.

Re: Need to get Last year data

sunny_talwar — Thu, 15 Feb 2018 14:49:49 GMT

Something like this

T1:

LOAD Sales_Organization,

Cat,

Calendar_Month_Code,

Calendar_Year_Code,

Consumption

FROM [T1.qvd](qvd)

Where Calendar_Year_Code>='2016' ;

LinkTable:

LOAD DISTINCT Calendar_Month_Code as AsOfCalendar_Month_Code,

Calendar_Month_Code,

'CY' as Flag

Resident T1;

Concatenate (LinkTable)

LOAD DISTINCT Calendar_Month_Code as AsOfCalendar_Month_Code,

AddYears(Calendar_Month_Code, -1) as Calendar_Month_Code,

'PY' as Flag

Resident T1;

Re: Need to get Last year data

rubenmarin — Thu, 15 Feb 2018 14:51:01 GMT

Then CalendarMonthCode can be also changed to add one year:

Date(AddYears(Calendar_Month_Code, 1), 'YYYYMM') as Calendar_Month_Code

if the data is aggregated a last join can be done to reduce rows and have Consumption and LYConsumption in the same row.

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 14:55:51 GMT

Thanks a lot Sunny..

But only one help needed. Is it possible to derive this expression what you have used in chart =Sum({<Flag = {'PY'}>}Consumption)

as a field called Cons_LY in script it-self..

Re: Need to get Last year data

sunny_talwar — Thu, 15 Feb 2018 15:02:23 GMT

Do you want to aggregate this in the script also?

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 15:04:57 GMT

Yes only in script i need to do for current year cons and last year cons and in chart directly i can load only those fields..

Re: Need to get Last year data

sunny_talwar — Thu, 15 Feb 2018 15:08:39 GMT

No, what I mean is that you might have multiple rows for each Sales_Organization, Cat and MonthYear.... so when we join... it sort of do a Cartesian join causing the numbers to increase. Can we sum up consumption to show at the level of Sales_Organization, Cat, and MonthYear for This year and last year?

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 15:13:40 GMT

Yes Sunny, We need to do that one only.

But even i have 4 to 5 years more there won't be any problem right. Because later i will data from 2015 to 2019

Now what ever the solution you have provided is what i needed but instead of writing those two expressions i need to get directly fields because later i will create qvd's..

Please let me know if i explained you correctly

Re: Need to get Last year data

sunny_talwar — Thu, 15 Feb 2018 15:17:32 GMT

May be this

T1:

LOAD Sales_Organization,

Cat,

Calendar_Month_Code,

Calendar_Year_Code,

Sum(Consumption) as Consumption

FROM [T1.qvd](qvd)

Where Calendar_Year_Code>='2016'

Group By Sales_Organization, Cat, Calendar_Month_Code, Calendar_Year_Code;

Left Join (T1)

LOAD Sales_Organization,

Cat,

AddYears(Calendar_Month_Code, 1) as Calendar_Month_Code,

Consumption AS Cons_LY

Resident T1;

But notice that your number of rows went down from 10,170 to just 170..... Not sure if that is what you want

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 15:28:08 GMT

Sorry Sunny to ask you for many times.

Please find the below image which you have given the solution and this what i needed:

But the set analysis what you have used for getting this year cons and last year cons is it possible to derive those fields in script it-self. Because the left join option what you have given is not working as like the above image.

Is it possible to derive those..

Re: Need to get Last year data

bharatkishore — Thu, 15 Feb 2018 15:30:06 GMT

Thanks Ruben

Re: Need to get Last year data

sunny_talwar — Thu, 15 Feb 2018 15:43:36 GMT

What's the difference?