Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
akuttler
Creator
Creator

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)?

5 Replies
jwjackso
Specialist III
Specialist III

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
Specialist
Specialist

Or

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

akuttler
Creator
Creator
Author

I tried this expression and its giving me NULL

joseduque
Partner - Contributor III
Partner - Contributor III

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
Creator
Creator
Author

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