Trailing Twelve Months Distinct User Count

[tealowk]

Dear Community, 

I have a source table with user activities. My task is to show the number of active users for each month of the past 12 months respectively.

My formula to calculate the measure looks like this:

count({<[Logon Date] = {">= $(=addmonths(date(max([Logon Date]), 'DD.MM.YYYY'), -11)) <= $(=monthend(date(max([Logon Date]), 'DD.MM.YYYY')))"} >}
distinct [User ID])

I use this measure in a pivot table with Year dimension as rows and Month dimension as columns. 

The data shown is limited to the active users per month - is there any possibility to disregard the Month dimension and calculate the measure on the given data set?

Thanks in advance for your help!

Regards,

Tealow

[jwjackso]

For rolling/trailing months, I always recommend Henric's As-Of-Table .  Your  date dimension would be [As-Of-Date] and the measure would become

count({<MonthDiff={"<12"}>}Distinct [User ID])

[tealowk]

Hi Jwjackso,

thanks for your reply. I've added the As-Of-Table but the values in the pivot table still show the distinct users in the respective month. 

As the dimensions constrain the calculations, I think I should the TOTAL qualifier. But the TOTAL qualifier overrides the {<MonthDiff={"<12"}>} set expression as well.

This is how the table currently looks like and - as you can see in the Totals column - the value for 12 months should be significantly higher.

[jwjackso]

What is the formula for the Totals column?  the count({<Month={'Oct','Nov',...'Sep'}>}Distinct [User ID]) can be much smaller than count({<Month={'Oct}>}(Distinct [User ID])+...+count({<Month={'Sep'}>}Distinct [User ID])

[tealowk]

There's no special formula for the Totals column - it shows up automatically when I select the "Show totals" option.

[jwjackso]

How are you getting the totals column.  The Qlik Sense pivot table puts the totals as the top row or or rows for partial sums.

[mato32188]

Hi @tealowk ,

do you use "Auto" for Totals function or do you use "Sum".

The first one gives you the answer, how many unique UserIDs you have in total (not per month, but overall in months), the second one simply sums your values presented in a row/column.

BR

m

[tealowk · Accepted solution]

Hi friends, 

I found a solution for this topic:

First step:  I modified @hic 's As-Of Calendar in the following way.

// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
  AsOfMonth,
  Month(AsOfMonth) as ReportMonth,
  Year(AsOfMonth) as ReportYear
  Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
  Year(AsOfMonth)-Year(Month) as YearDiff
  Resident tmpAsOfCalendar
      Where AsOfMonth >= Month and
      Round((AsOfMonth-Month)*12/365.2425) >= 11;

Now,  for every real month I have the twelve following months in my As-Of Calendar.

Second step:  I generate the the visualizations based on the dimensions in the As-Of Calendar. (ReportYear as row, ReportMonth as columns)

The pivot table now looks like this:

 And the corresponding bar chart like this:

Thanks @all for your help and inspiration!