Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
akuttler
Creator
Creator

Current Month Average Daily Charges

Hello,

I am trying to write an expression that gives me average daily charges:

(Sum({<ITEMTYPE={'1000','1001'}, Date = {"$(=Max(Month(Date)))"}>}Trans)) / (Count(Distinct [Service Date]))

Where ITEMTYPE = 1000,1001 represents charge transactions

I've tried several things but they all give me zero or null.

Thanks in advance.

1 Solution

Accepted Solutions
Thiago_Justen_

Ashley,

I guess you must to change your expression a little bit:

(Sum({<ITEMTYPE={'1000','1001'}, Date = {">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"}>}Trans)) / (Count(Distinct [Service Date]))

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago

View solution in original post

3 Replies
Thiago_Justen_

Ashley,

I guess you must to change your expression a little bit:

(Sum({<ITEMTYPE={'1000','1001'}, Date = {">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"}>}Trans)) / (Count(Distinct [Service Date]))

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
akuttler
Creator
Creator
Author

Thanks, I realized I would also have to apply the set modifier to the denominator so :

Current Month Average Daily Charges:

(Sum({<ITEMTYPE={'1000','1001'}, Date = {">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"}>}Trans)) / (Count({<ITEMTYPE={'1000','1001'}, Date = {">=$(=MonthStart(Max(Date)))<=$(=Max(Date))"}>}Distinct [Service Date]))

Thiago_Justen_

Pretty good Ashley!

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago