Exclude zero values in Rangeavg

[aendr3w]

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.

cheers! 

Andreas

[lorenzoconforti]

Maybe:

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

Can you post your dashboard?

[aendr3w]

Hi, Unfortunately it didn't help.

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

[aendr3w]

How do I post my Dashboard?

/Andreas

[lorenzoconforti]

What version of Qlik Sense are you using? Desktop?

[aendr3w]

I'm using Qlik Sense Enterprise

Qlik Sense November 2018

qliksenseserver: 12.44.1

[lorenzoconforti]

see here:

https://help.qlik.com/en-US/sense/June2019/Subsystems/ManagementConsole/Content/Sense_QMC/export-app...

[sebastiandperei · Accepted solution]

Hi Andreas!

Use as dimmension:

if( weekday(date)<5,date)

[aendr3w · Accepted solution]

@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.