Current Month YTD Values minus Previous Month YTD Value

[bhaskar_sm]

Dear All,

           I am stuck in a situation  where I have Monthly value as YTD as input value ( Cumulative value of previous month) i.e, for the month of May the value is April value + May value and so on. I need to find the FTM value of each month. In order to find the monthly value I need to subtract the  Current Month YTD value - Previous month YTD value i.e,  MAY FTM = MayYTDValue - AprilYTDValue. I have also attached the sample data.

NOTE: I need to achieve this in the back-end

Any help is appreciated.

Regards,

Bhasker Kumar

[maxgro]

SCE:

LOAD

  //Category & '|' & Segment & '|' & Subsegment & '|' &  Manuacturer & '|' & Year as Id, 

  rowno() as rn,

  Year as Id,

  Category,

    Segment,

    Subsegment,

    Manuacturer,

    Month,

    Year,

    [YTD Production]

FROM

senario.xlsx

(ooxml, embedded labels, table is Sheet1);

NEWSCE:

load

  *,

  if(Year=Peek('Year'), [YTD Production] - peek('FTM'), [YTD Production]) as FTM,

  Peek('FTM') as PrevFTM

Resident SCE

order by rn;

DROP Table SCE;

[perumal_41]

HI,

Have a look at the link for help.

Point-in-Time Reporting.qvw

Regards,

Perumal

[#4]

Hi @Bhasker,

Were you able to achieve the result? I am stuck with the same thing.

Regards,

Nimisha

[bhaskar_sm · Accepted solution]

Hi Nimisha,

Please find the solution script .

******************************************MAIN*****************************************************************

Set vFM = 4 ;                                                          // First month of fiscal year

DATA_RAW:

Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

         Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

          *;

Load Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

         Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

          *;

Load *,Year(Date)                              as Year,           // Your standard master calendar

         Month(Date)                            as Month;

LOAD

     AutoNumberHash128(Category,Segment,[Sub Segment],Manufacturer) as PK ,

     Category,

     Segment,

     [Sub Segment],

     Manufacturer,

     [YTD Period] as Date,

     YTD,

    // Date(Date#([YTD Period],'MMM-YYYY')) as Date,

     num(month(YTD)) as Month1,

     num(MM)  as monthnum1,

     MMYYY,

     MM,

     YYYY,

     Production,

     [Domestic Sales],

     Exports

FROM

(qvd);

DATA_STAGE1:

LOAD

  PK,

  Category,

     Segment,

     [Sub Segment],

     Manufacturer,

     //[YTD Period],

     Date,

     fMonth,

  fYear,

  sum(Production) as Production

Resident DATA_RAW Group by

  PK,

  Category,

     Segment,

     [Sub Segment],

     Manufacturer,

    // [YTD Period],

     Date,

     fMonth,

  fYear;

Drop Table DATA_RAW;

DATA_STAGE2:

Load rowno(),

PK,

AutoNumberHash128(Category,Segment,[Sub Segment],Manufacturer,Date) as PK_GLOBAL,

  Category,

     Segment,

     [Sub Segment],

     Manufacturer,

     Date,//[YTD Period],

     fMonth,

  fYear,

  'hi' as Tend,

  Production,

  if(rowno()=1,rangesum(Production,0)

   ,if(Peek(PK)=PK and peek(fYear)=fYear,-1*(rangesum(Peek(Production),0)-numsum(Production)),rangesum(Production,0))) as CurrentMonthValue

RESIDENT DATA_STAGE1 order by

Category,

     Segment,

     [Sub Segment],

     Manufacturer ,fYear,fMonth ;

DROp TABLE DATA_STAGE1; 

************************************************************END OF SCRIPT********************************************

solution given by Prem Kumar Thangallapally

Regards,

Bhasker Kumar

[tripatirao]

Dear Bhasker,

Is data.qvd created from the excel which you have attached here?

i am having one doubt in the excel file which you have attached here,

Can you please explain in number how may month data is sum of apr and may.

Regards

Tripati