Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
avantime
Creator II
Creator II

Daily average calculation issues

Hi all,

I need to calculate the daily average sales for each customer.

Right now I use this formula:

Sum(Sales)/sum({1<Year = $::Year , Month=$::Month, Day=$::Day>} TOTAL <Year,Month,Day> [Working Day Marker])

I load the working day marker from a calendar I made, containing all days in a year, the marker being 0 for weekends or legal holidays and 1 for everything else.

The formula works great if no filters are applied for Year or Month or Day, otherwise it gives the wrong results.. 

Anyone got a solution for this?

Basically what I want to do is to split customer revenue to the total number of working days in selected period.

Thanks!

 

4 Replies
gn_marvs1989
Contributor III
Contributor III

Have you tried to use Aggr() instead of TOTAL? 

If you are calculating per dimension on one of your Sheet objects or Charts, you might need to use Aggr()

e.q. Avg Sales per Customer  in a straight table.

  

avantime
Creator II
Creator II
Author

I don`t need Avg. 

I need to divide the clients sales to the number of working days in the period selected (Years, Months, Days), not to the number of days he made purchases in.

So, by example, let`s say the client made 3 purchases in 3 different days of Jan 2019, if I filter the date to Month Jan and Year 2019, I need to split the clients sales to 20 working days in my country for January.

Hope I made myself understood.

 

Thanks!

avantime
Creator II
Creator II
Author

Up! Any other ideas?

avantime
Creator II
Creator II
Author

I`m also attaching a test qvw, it`s easier to understand what i want.