Rolling 12 Months Count Distinct YearMonth

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

Can you guide me how to make set analysis running 12 months count distinct yearmonth? I've tried using set analysis like in the below:

[MM] = Sum(Aggr(RangeSum(Above(Count({<Type2={'SHIPMENT', 'RET. SHIPMENT'}, DateId={"<=$(Today())"},$(='['&Concat({<$Table={'dCalendar'}, $Field-={'DateId'}>}DISTINCT $Field, ']=, [')&']=')>}DISTINCT MonthNameId) + Sum({<DateId=>}0), 0, RowNo())), ItemNo, (MonthNameId, (NUMERIC))))

The problem are:

1. Count cumulative for entire dimension MMM YYYY

2. When the dimension change into Week or Date, it has 0 value and also cumulative for entire Date dimension

The result what I want like in the below for each date dimension:
Month Year

Year Week

DateId

Attached my .qvw file

topic Rolling 12 Months Count Distinct YearMonth in QlikView

Rolling 12 Months Count Distinct YearMonth