topic Last n Months in Script in QlikView

Last n Months in Script

Anonymous — Sat, 02 Dec 2017 04:45:44 GMT

Hi,

I have a need to calculate the last 6 months (excluding the reported month) Sales for a given reported date in the sample provided.

For instance, Customer 20170 will have the following details once completed in the data model.

11/2017 - $343,913

10/2017 - $343,562

I have tried the concept with a IF...previous and RangeSum with Peek but I cannot do something similar to Above function in Script.

Appreciate your assistance.

Re: Last n Months in Script

Kushal_Chawda — Sat, 02 Dec 2017 09:26:24 GMT

see this

Rolling N months

Re: Last n Months in Script

antoniotiman — Sat, 02 Dec 2017 11:30:31 GMT

You need As-Of Table, like this

Data:
LOAD Customer,
Amount,
WorkDays,
ReportedDate,MonthName(ReportedDate) as Month
FROM
SampleDataSet.xls
(biff, embedded labels, table is Sheet1$);
Temp:
LOAD DISTINCT Month
Resident Data;
Join
LOAD Month as AsOfMonth
Resident Temp;
[As-Of Table]:
Load Month,
AsOfMonth,
Round((AsOfMonth-Month)*12/365.2425) as MonthDiff
Resident Temp
Where AsOfMonth >= Month;
Drop Table Temp;

Re: Last n Months in Script

Anonymous — Sat, 02 Dec 2017 20:17:49 GMT

‌thanks for sharing the conept. the concept will work well I believe in visualisations using objects with set analysis. however what I am after is a method to build this to a fact table that could be used in some other calculations with other data in later steps.

Re: Last n Months in Script

Anonymous — Sat, 02 Dec 2017 20:23:26 GMT

MAny thanks for demonstrating the concept. ‌‌this will work well in sheets with visualisation objects using set analysis. however what I am after is a method to build this to a fact table that could be used in some other calculations with other data in later steps. So I would need something that combines both tables to a single table for 6 month total based on prior month to selected month. Any ideas?

Re: Last n Months in Script

Anonymous — Sun, 03 Dec 2017 00:08:18 GMT

I have managed to figure out a technique by the use of "Flags".

1. Associate a distinct set of Months with the base data load by left joining a new date field to the base load. 1000 rows of base load with 12 distinct months will become 12,000 rows. (New Field = CalcDate)

2. Use Flag to identify which rows are applicable for calculation. ie.

,IF(ReportedDate > Addmonths(CalcDate,-7) and ReportedDate < CalcDate,1,0) as Flag

Note the load to be in ReportedMonth DESC order.

3. Left Join Base Table with Aggrgated values for each Customer, reporting month where Flag = 1

Re: Last n Months in Script

kdr_Qv — Fri, 05 Jun 2020 23:06:45 GMT

HI .. good example

same way.. instead of Month i have date field in my dimension.. so..

if i apply same script for date last 3 months sales excluding current month... its not working(its splitting by date wise by default).. could u please suggest me how to do that..

My dimension - Date

Measure 1 --> Sum(Sales) each date wise

Measure(2)--> last 3 month sales --> here excluding current month so it will be common for all respected month

@Gysbert_Wassenaar @hic @tresB @Antoniotiman