How to idenitfy month from previous year with the highest count of customers

[KenDearman]

Hi Experts

I'm new to Qlik Sense (to Qlik in general) and I've hit a roadblock trying to create a dashboard.

I have two fields:

• A transaction time/date field that has been loaded as a timestamp [TransactionTime]
• A customer ID field that is numeric [CustomerID]

I would like to do the following but cannot find a solution: I need to know the highest number of transactions from the previous 12 months, i.e. what was the peak number of transactions per month in the previous 12 months.

What I'm ultimately trying to do is then generate a % of the (number of transactions last month/the most number of monthly transactions in the last year). So if we have 1000 transactions last month and the best month last year had 2000 transactions then I would report 50%.

Many thanks in advance

Ken

[lironbaram]

hi 

first i would have created a month field in your load script 

using this function : monthstart(TransactionTime) as TransactionMonth

then you can use this function :

assuming you selected current month your expression will be 

count (CustomerID)/ max(aggr(count({<TransactionMonth ={">=$(addmonths(max(TransactionMonth ),-12))"}>}CustomerID),TransactionMonth))

[KenDearman]

Thank you for the very quick response. I've just realised that as I'm working remotely I cannot update my load scripts, etc and test your solution (I only have the QVF file and not the data files). However, once I am back at my desk I'll test it properly.

However, I can see what you solution is doing and that's moved my learning by a large factor!

Cheers

Ken