topic Re: Daily average calculation issues in QlikView

Daily average calculation issues

avantime — Fri, 08 Feb 2019 05:35:46 GMT

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!

Re: Daily average calculation issues

gn_marvs1989 — Fri, 08 Feb 2019 08:50:33 GMT

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.

Re: Daily average calculation issues

avantime — Fri, 08 Feb 2019 16:46:32 GMT

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!

Re: Daily average calculation issues

avantime — Tue, 19 Feb 2019 10:52:52 GMT

Up! Any other ideas?

Re: Daily average calculation issues

avantime — Tue, 19 Feb 2019 16:04:01 GMT

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