Last n Months in Script

[Anonymous]

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.

[Kushal_Chawda]

see this

Rolling N months

[antoniotiman]

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;

[Anonymous]

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

[Anonymous]

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?

[Anonymous]

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

[kdr_Qv]

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  @tresesco   @Antoniotiman