Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]))
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]))
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]))
Pretty good Ashley!