Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
SaiKiran2
Contributor
Contributor

Cumulative sum with default view to show 6 months

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.

Dimensionif(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 

Labels (5)
1 Reply
SaiKiran2
Contributor
Contributor
Author

Using same logic, I checked for Cumulative count which is working as expected but I have an issue in calculating percentage.
Measurecount(distinct{<[cancelled_flag]={'Y'}>}[patient_id])