Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

akuttler
New Contributor III

Average Daily Charge Most Recent 6 months

Hello,

The following expression gives me average daily charges:

(Sum({<ITEMTYPE={'C'}>}Trans)) / (Count(Distinct  ([Transaction Date])))

How would I re-write it to give me average daily Charges only for the most current 6 months (transaction date)?

Tags (1)
5 Replies
jwjackso
Contributor III

Re: Average Daily Charge Most Recent 6 months

Try

(Sum({<ITEMTYPE='{'C'},[Transaction Date]={">=$(=Date(AddMonths(Now(),-6)'DD/MM/YYYY'))"}>}Trans)/

Count({<[Transaction Date]={">=$(=Date(AddMonths(Now(),-6)'DD/MM/YYYY'))"}>} Distinct [Transaction Date]))


You will need to adjust the Date format to your date.  Check out calendars if you don't use them (Creating A Master Calendar)

zebhashmi
Valued Contributor

Re: Average Daily Charge Most Recent 6 months

Or

if ([Transaction Date]>AddMonths([Date],-6),(Sum({<ITEMTYPE={'C'}>}Trans)) / (Count(Distinct  ([Transaction Date]))))

akuttler
New Contributor III

Re: Average Daily Charge Most Recent 6 months

I tried this expression and its giving me NULL

joseduque
New Contributor III

Re: Average Daily Charge Most Recent 6 months

Set Analysis is your best choice,  but just to make sure  you need the average for each daily separately or could be a sum of the 6 months transaction divide to 6 months transaction date?

akuttler
New Contributor III

Re: Average Daily Charge Most Recent 6 months

I just need the sum of 6 months divided by 6 months of transactions dates.