Calculating Last Month Same Day Sales

[rahulsingh12]

Hi,

I wanted to calculate last month same day sales in the QlikView script.

Eg: If today 18th Mar 2018 then for the same set of combination of State, brand, depot  I want to get the sales for 18th Feb 2018 and 18th Jan 2018.

All the 3 values should come as a separate column in table.

I know we can achieve it using expressions in charts but I want to calculate it in the script and get it as two separate columns.

Regards,

[tresesco]

Could you share a sample app with sample data set and explain the expected output?

[rahulsingh12]

Hi,

Attaching a sample output.

Consider the current date be 1st Aug

Regards,

[HirisH_V7]

Hi Check out this code below,

As i can understand, your'e trying to compress the data set with every load. To load only required from your'e data.

Data:
LOAD [State Name], [State Cluster], GAP, [Depot Name], [Depot Code], Brand, [Brand Family], 
     [Brand Pack Code], [Size Pack Code] ,
     Sum(Current_Month_Today) as Current_Month_Today,
  	  Sum(Prior1_Month_Today) as Prior1_Month_Today,
      Sum(Prior2_Month_Today) as Prior2_Month_Today
      Group By [State Name], [State Cluster], GAP, [Depot Name], [Depot Code], Brand, [Brand Family], 
     [Brand Pack Code], [Size Pack Code];
LOAD [State Name], [State Cluster], GAP, [Depot Name], [Depot Code], Brand, [Brand Family], 
     [Brand Pack Code], [Size Pack Code], 
  If(Date=Date(Today()),Plan) as Current_Month_Today,
  If(Date=Date(AddMonths(Today(),-1)),Plan) as Prior1_Month_Today,
  If(Date=Date(AddMonths(Today(),-2)),Plan) as Prior2_Month_Today
  
FROM [Sample.xlsx]
(ooxml, embedded labels, table is Sheet2);

Hope this will get the required fields and aggregate in back end. To create a summarized view as required.

PFA For ref.

HTH,

Hirish 

[rahulsingh12]

Hi,

I think this will only give me the date for last 2 months but not the sales on those days.

Regards,

[HirisH_V7]

Assuming plan column as Sales, i have summed up the same in every new column created.

Let me know if any other column like sales need to be taken care of, anyway that also can be summed up to get a desired result.

For ref look into sample data in sheet2 of my attachment.