Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
aendr3w
Contributor II
Contributor II

Exclude zero values in Rangeavg

Hi!

I'm trying to create a line chart with the average value of the previous 10 non-zero sales values using Rangeavg. This is to make the curves smoother.

However, i can't seem to find a way to exclude the the zero values I get on Saturdays and Sundays. Is there a way to exclude them? I will compare different years using =DayNumberOfYear() as dimension so i can't exclude weekends in the dimension.

This is the code i'm using is:

rangeavg(above(
sum({$<[Date of Entry (Year)] ={$(vActualYear)},
= >}[# Net Amount Order]),0,2))

(Will change to ,0,10 when i get it to work)

 

The line chart is displaying my result. Blue line is set to average 2 days and the red is set to 1 day (no average)

Day 54 is a Sunday with 0 Net amount.

RangeavgChart.jpg

 

RangeavgTable.jpg

cheers! 

Andreas

2 Solutions

Accepted Solutions
sebastiandperei
Specialist
Specialist

Hi Andreas!

 

Use as dimmension:

if( weekday(date)<5,date)

 

View solution in original post

aendr3w
Contributor II
Contributor II
Author

@sebastiandperei 

You pushed me in the right direction. Thank you.

I solved it using this code as dimension:

=if( weekday([Date of Entry])<5,week([Date of Entry])&num(weekday([Date of Entry])))

Now i can compare multiple years in the same chart without having any weekend dates involved.

View solution in original post

8 Replies
lorenzoconforti
Specialist II
Specialist II

Maybe:

rangeavg(above(sum({$<[Date of Entry (Year)] ={$(vActualYear)},[# Net Amount Order]={">0"}>}[# Net Amount Order]),0,2))

Can you post your dashboard?

aendr3w
Contributor II
Contributor II
Author

Hi, Unfortunately it didn't help.

With [# Net Amount Order]={">0"} i get no chart at all. 

aendr3w
Contributor II
Contributor II
Author

How do I post my Dashboard?

/Andreas

lorenzoconforti
Specialist II
Specialist II

What version of Qlik Sense are you using? Desktop?

aendr3w
Contributor II
Contributor II
Author

I'm using Qlik Sense Enterprise

Qlik Sense November 2018

qliksenseserver: 12.44.1

sebastiandperei
Specialist
Specialist

Hi Andreas!

 

Use as dimmension:

if( weekday(date)<5,date)

 

View solution in original post

aendr3w
Contributor II
Contributor II
Author

@sebastiandperei 

You pushed me in the right direction. Thank you.

I solved it using this code as dimension:

=if( weekday([Date of Entry])<5,week([Date of Entry])&num(weekday([Date of Entry])))

Now i can compare multiple years in the same chart without having any weekend dates involved.

View solution in original post