Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
q11hhg
Contributor III
Contributor III

Last 12 month expression with table chart

Hi there, I have an expression to calculate the last 12  months customers. It works fine with a KPI chart.


Count(distinct{$<
Date={">=$(=date(addmonths(Max(Date),-12),'YYYY-MM-DD'))<=$(=date(Max(Date),'YYYY-MM-DD'))"}
,[Event Type]={'Order'}>}
[Customer ID])

But when I put this expression in a table chart, the result is not what I expected.

q11hhg_0-1614854681312.png

The expression in the table chart only gives me the customers in that month, it does not calculate for the last 12 months. For example in 2020-Apr, the number should be 929.

What I want to achieve is to let the expression calculate the last 12 months customers based on the Year Month on each row. so the year month column should be used as the max date in the expression.

could you please help me?

 

Labels (2)
5 Replies
rbartley
Specialist
Specialist

Try this:

 

=RangeSum(Below(Sum([Customer]),0,12))

 

It should work so long as you're table is ordered from most recent to earliest, otherwise exchange the Below for Above

q11hhg
Contributor III
Contributor III
Author

Hi @rbartley  I do not have a [Customer] column in my data set . 

What does the [Customer] referring to ?  

rbartley
Specialist
Specialist

Just replace Sum([Customer]) with the aggregation function and field that applies, to you e.g. Count([Customer ID]). The RangeSum() function sums over a range of values, which in this case takes additional parameters Below(), the starting index (0) being the current record, and the number of values to sum over, i.e. 12.

q11hhg
Contributor III
Contributor III
Author

ok, I still do not understand, I just started to use this tool. Could you please tell me how should I adjust my expression to let it calculate correctly in a table chart, on each row. 
Count(distinct{$<
Date={">=$(=date(addmonths(Max(Date),-12),'YYYY-MM-DD'))<=$(=date(Max(Date),'YYYY-MM-DD'))"}
,[Event Type]={'Order'}>}
[Customer ID])

rbartley
Specialist
Specialist

Please export your app and attach your qvf file here so that I can update it.