Need to get Last year data

[bharatkishore]

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

[Anil_Babu_Samineni]

May be add this in script?

Left Join(T1)

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

[bharatkishore]

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';

[Anil_Babu_Samineni]

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;

[bharatkishore]

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

[rubenmarin]

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

[sunny_talwar]

Can you share T1.qvd?

[bharatkishore]

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

[bharatkishore]

Hi Ruben,

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

[sunny_talwar · Accepted solution]

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;