topic Re: Average Sales by Days in QlikView

Average Sales by Days

oseimuduame — Mon, 26 Jan 2026 18:19:17 GMT

Good Morning Guys,

I am having issue with a report on Qlikview, i want to be able to run an average sales report by day. This means that if i made a sales of 100000 in a month that the invoice days is 28, i want to be able to get the average sales.

Avg = Total Sales / No of Invoice Days

Note: i am suppose to use Avg = Total Sales / No of Days but there are days when no sales was made.

I used AVG(INVC_PRICE) but not getting the correct result.

Any help please

Re: Average Sales by Days

YoussefBelloum — Mon, 16 Jul 2018 12:02:28 GMT

Hi,

would you be able to share sample app or sample data ?

Re: Average Sales by Days

sunny_talwar — Mon, 16 Jul 2018 12:07:39 GMT

May be this

Sum(Sales)/Count({<Date = {"=Sum(Sales) > 0"}>}Date)

Re: Average Sales by Days

oseimuduame — Tue, 17 Jul 2018 10:43:10 GMT

Hi Sunny,

I used the above script and i still not get the right answer.

For Example i run a report for a store that sold 590150 and the total number of days invoice was raised for the store is 15, i am suppose to have 590150/15 but instead my result was 23606.

Any idea please ?

Re: Average Sales by Days

oseimuduame — Tue, 17 Jul 2018 10:43:48 GMT

For Example i run a report for a store that sold 590150 and the total number of days invoice was raised for the store is 15, i am suppose to have 590150/15 but instead my result was 23606.

Re: Average Sales by Days

sunny_talwar — Tue, 17 Jul 2018 11:07:09 GMT

Can you check the values for each part of the expression

1) Sum(Sales)

2) Count({<Date = {"=Sum(Sales) > 0"}>}Date)

See which of the two are not giving the right values.... and then we can check what we can do next... Also, an image might help see the issue better

Re: Average Sales by Days

oseimuduame — Tue, 17 Jul 2018 14:50:31 GMT

Hi Sunny,

i think the second script has the issue in the sense that it is not giving the correct figure.

the script is to count the number of days invoice was raised but currently it is not

Re: Average Sales by Days

sunny_talwar — Tue, 17 Jul 2018 15:00:16 GMT

What is the number it is giving and what do you expect to see?

Re: Average Sales by Days

oseimuduame — Tue, 17 Jul 2018 15:02:31 GMT

From the above Screenshot;

1) Sum(Sales) = Total Sales

2) Count({<Date = {"=Sum(Sales) > 0"}>}Date) = Count

This is a filter for a Store and the Day sales was made is the white areas under DAY.

Day = 15 but the Formula is giving me 25

Re: Average Sales by Days

bbmmouha — Tue, 17 Jul 2018 15:02:32 GMT

Avg( Aggr( Sum(sales), Day ) )

Re: Average Sales by Days

sunny_talwar — Tue, 17 Jul 2018 15:04:32 GMT

Can you try with Count(Distinct....)

Count(DISTINCT {<Date = {"=Sum(Sales) > 0"}>}Date)

Re: Average Sales by Days

sunny_talwar — Tue, 17 Jul 2018 15:05:04 GMT

So, this...

Sum(Sales)/Count(DISTINCT {<Date = {"=Sum(Sales) > 0"}>}Date)

Re: Average Sales by Days

oseimuduame — Tue, 17 Jul 2018 15:05:20 GMT

I now used

Count(DISTINCT {<INVC_DAY = {"=Sum(INVC_EXT_PRICE) > 0"}>}INVC_DAY)

and it was close. Now i am getting 14 as my number of days

Re: Average Sales by Days

sunny_talwar — Tue, 17 Jul 2018 15:06:47 GMT

How about if you do this

Count(DISTINCT INVC_DAY)

Re: Average Sales by Days

sunny_talwar — Tue, 17 Jul 2018 15:07:37 GMT

Or this

Count(DISTINCT {<INVC_DAY = {"=Sum(INVC_EXT_PRICE) <> 0"}>}INVC_DAY)

Re: Average Sales by Days

oseimuduame — Tue, 17 Jul 2018 15:09:19 GMT

Dear Sunny,

The above script works very well now checking and comparing with the other stores.

See image below. Thanks So much

Re: Average Sales by Days

oseimuduame — Tue, 17 Jul 2018 15:15:33 GMT

This works very fine. Just the way i wanted

See Below

Exactly what i needed

Re: Average Sales by Days

sunny_talwar — Tue, 17 Jul 2018 15:21:08 GMT

Super

Re: Average Sales by Days

bbmmouha — Tue, 17 Jul 2018 15:26:43 GMT

share the solution plz and close it as solved