topic Re: Calculating Last Month Same Day Sales in QlikView

Calculating Last Month Same Day Sales

rahulsingh12 — Sat, 16 Nov 2024 04:08:29 GMT

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,

Re: Calculating Last Month Same Day Sales

tresB — Mon, 18 Mar 2019 07:25:28 GMT

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

Re: Calculating Last Month Same Day Sales

rahulsingh12 — Mon, 18 Mar 2019 07:41:18 GMT

Hi,

Attaching a sample output.

Consider the current date be 1st Aug

Regards,

Re: Calculating Last Month Same Day Sales

HirisH_V7 — Mon, 18 Mar 2019 08:52:45 GMT

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

Re: Calculating Last Month Same Day Sales

rahulsingh12 — Mon, 18 Mar 2019 09:52:01 GMT

Hi,

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

Regards,

Re: Calculating Last Month Same Day Sales

HirisH_V7 — Tue, 19 Mar 2019 06:49:13 GMT

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.