topic Re: Last 7 days average barchart in QlikView

Last 7 days average barchart

razvan_brais — Wed, 25 Nov 2020 16:16:04 GMT

I`m trying to calculate last 7 days average of sales and put it in a bar chart , but when I add the expression it shows values for every day. I want to show just one bar for the average.

This is the expression:

sum({<DATE = {">=$(=Date(GetFieldSelections(DATE)-7))<=$(=Date(GetFieldSelections(DATE)))"}>} Sales) / 8

What is wrong , or what am I doning wrong?

Thank you

Re: Last 7 days average barchart

mohammadkhatimi — Fri, 30 Sep 2016 09:44:31 GMT

What u r adding in dimension tab DATE or Month???

Re: Last 7 days average barchart

razvan_brais — Fri, 30 Sep 2016 09:48:51 GMT

Date

Re: Last 7 days average barchart

mohammadkhatimi — Fri, 30 Sep 2016 09:51:44 GMT

Use Month..

Re: Last 7 days average barchart

Anonymous — Fri, 30 Sep 2016 09:53:35 GMT

Try this:

sum({<DATE = {'>=$(=Max(DATE)-7)<=$(=Date(Max(DATE))'}>} Sales) / 8

Regards!!

Re: Last 7 days average barchart

razvan_brais — Fri, 30 Sep 2016 09:53:38 GMT

Well I kinda need the Date dimension , because in the same bar chart I will have total sale for today , total sales for a week ago and the average for last 7 days

Re: Last 7 days average barchart

Fri, 30 Sep 2016 10:00:02 GMT

sum({$<DATE = {">=$(=Date(max(DATE)-7))"}>} Sales) / 7

try this please

Re: Last 7 days average barchart

razvan_brais — Fri, 30 Sep 2016 10:21:07 GMT

I wrote this expresion , but the result is not shown on my chart

Re: Last 7 days average barchart

Anonymous — Fri, 30 Sep 2016 10:44:37 GMT

Hi,

Create two vars:

vMaxDate = '=Date(Max(Date))' (without quotes)

vMaxDatePrev = '=Date(Max(Date)-7)' (without quotes)

sum({<DATE = {'>=$(vMaxDatePrev)<=$(vMaxDate)'}>} Sales) / 8

Regards!

Re: Last 7 days average barchart

razvan_brais — Fri, 30 Sep 2016 10:56:21 GMT

It still shows me bars for each day. I would like to make only one bar that shows the total sum for a week

Re: Last 7 days average barchart

giakoum — Fri, 30 Sep 2016 10:57:33 GMT

aggr(sum({<DATE = {'>=$(vMaxDatePrev)<=$(vMaxDate)'}>} Sales) / 8, Week)

Re: Last 7 days average barchart

Anonymous — Fri, 30 Sep 2016 11:13:53 GMT

Could you share one example of your app plz?

Re: Last 7 days average barchart

sunny_talwar — Fri, 30 Sep 2016 11:37:50 GMT

Are you looking for something like this?

Re: Last 7 days average barchart

razvan_brais — Fri, 30 Sep 2016 13:06:28 GMT

I don`t understand why it shows me only two bars , the one for today and the one for 7 days ago , but the average is not shown

this is the exact query that I wrote

=If(Dimensionality() = 0,

Avg({<DATE = {"$(='>=' & Date(Max(DATE)-7) & '<=' & Date(Max(DATE)))"}, LEVEL1={'Electronics'}>}if(CUST_TYPE='P',SALES,SALES*-1)),

Sum({<DATE = {"$(=Date(Max(DATE)-7))", "$(=Date(Max(DATE)))"}, LEVEL1={'Electronics'}>}if(CUST_TYPE='P',SALES,SALES*-1)))

Re: Last 7 days average barchart

sunny_talwar — Fri, 30 Sep 2016 13:09:01 GMT

Apologize, I should have pointed that out. Select 'Show Total' on the dimension limits tab