Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Exclude non-possible DATES on chart

Hi,

I am facing a problem I can't solve ...

I want to represent the evolution of sales of one specific shop and compare it to the evolution of the sales of all others shops that have a similar sales revenue last month.

I made a combo chart with these specifications :

- Date as dimension

- Sum(sales) as Measure1

- Avg([set analysis] sales) as Measure2

My set analysis was made to pick among all registered shops the ones that have a similar sales revenue. It looks like this :

{ 1 <MonthlySales = {'>=$(=0.9*MonthlySales)<=$(=1.1*MonthlySales) '}>}

MonthlySales is a dimension.

It works fine for the calculation but my problem is the plotted chart : I have specific Dates where Sum(sales) = 0 for the shop I picked and I don't want these dates to appear on chart.

Unfortunately, dued to the {1 ....} of my set analysis and the fact that other shops have a Sum(sales) <> 0 on these dates, everyting is plotted on chart.

The option "include null values" for the chart is not checked, but it still plots all dates.

Any idea on how to work around this please ?

Here is a screen to clarify the situation.dates.PNG

A filter pane shows the possible values for the dimension DATES :

dates2.PNG

i tried to create a new dimension based on the "possible fields", but it's a dead end for me ...

Thanks for the consideration !

6 Replies
Anonymous
Not applicable
Author

Hi Tomasz,

Thanks for the guidance ! Unfortunately, I've never used the P() & E() functions, could you give me a hand on the structure that could help me ? I'm guessing in pseudo code, it would be something like :

Date = E( {<sum(sales) = {0}>} id_of_specific_shop)

antoniotiman
Master III
Master III

May be

{ 1 <MonthlySales = {'>=$(=0.9*MonthlySales)<=$(=1.1*MonthlySales) '},date=$::date>}

tomasz_tru
Specialist
Specialist

Other way:

1. for the measure2 use:

          if ( [measure1 formula]=0,0,[measure2 formula] )

2. In 'data handling' panel uncheck 'Show zero values'

Anonymous
Not applicable
Author

None of these techniques work for me 😕

I can't get the proper E() expression in my set analysis

For the IF idea, It plots the line even with the zero values on some dates (very strange ...)

Anonymous
Not applicable
Author

Thanks for the idea but it didn't work either