I have requirement where user want to see latest 6 months data ( which should show cumulative sum from entire data set).
LOAD
patient_id,
Cancelled_flag,
Date(month_year) as month_year,
if(isnull(month_year),null(),Month(date(month_year))&'-'&right(year(date(month_year)),2) ) as Month_S,
FROM Table1;
//====================== Below script is for cumulative report ========================//
tmpAsOfCalendar:
Load distinct month_year
Resident Table1
where month_year<>null();
Join (tmpAsOfCalendar)
Load month_year as month_year_cal
Resident tmpAsOfCalendar ;
NoConcatenate
[As-Of Calendar]:
Load month_year,
month_year_cal,
if(isnull(month_year_cal),null(),
Month(date(month_year_cal))&'-'&right(year(date(month_year_cal)),2) ) as Month_S_cal
Resident tmpAsOfCalendar
Where month_year_cal >= month_year;
Drop Table tmpAsOfCalendar;
//=====================================================================================
Below is the dimension and measure I have tried.
Dimension: if(GetSelectedCount(Month_S_cal)>0,Month_S_cal,
aggr(only({<[month_year_cal]={">=$(=date(monthstart(addmonths(max(date([month_year_cal])),-5))))<=$(=date(max(Date([month_year_cal]))))"}>} Month_S_cal),[month_year_cal]))
Measure: count(distinct{<[cancelled_flag]={'Y'}>}[patient_id])
/ Count(distinct patient_id)
I'm looking forward for suggestions to do this cumulative sum, Let me know if something is not clear!
@tresesco @sunny_talwar
