Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tertre_m
New Contributor II

Keep exclusive filters

Hello, I want to compare two time period from the same column and to have KPI showing the difference, I'm using exclusive filters to do this.

What I do currently is that I copied my date column in the load editor so I have one column date1, one column date2 and one column amount. I have two line chart, one contains date as dimension and sum({<date2=>} amount) as measure and the other one is similar, the KPI is sum({<date2=>} amount) - sum({<date1=>} amount).

When I filter the year 2016 in date1, it doesn't change the other line chart and it changes the KPI as expected, then, if I filter the year 2015 in date2, it deletes the first filter because no line is matched by both.

How can I still keep both filters without having any intersection between my filters ?

1 Solution

Accepted Solutions

Re: Keep exclusive filters

You might need to create an intersection of date1 and date2 in the script.... Something along these lines

Dates:

LOAD DISTINCT date1

FROM ....

Join (Dates)

LOAD DISTINCT date2

FROM ...

Now each date1 will have all possible date2 and each date2 will have all possible date1 available for selection.

9 Replies
Employee
Employee

Re: Keep exclusive filters

Dear Manuel,

  Take a look on these articles:

Set analysis – an introduction ‒ Qlik Sense

Set analysis – taking the next step ‒ Qlik Sense

Ricardo Gerhard
OEM Solution Architect
LATAM
vinieme12
Esteemed Contributor II

Re: Keep exclusive filters

Period Presets: Compare Periods on the fly

You can compare overlapping periods like this

Re: Keep exclusive filters

You might need to create an intersection of date1 and date2 in the script.... Something along these lines

Dates:

LOAD DISTINCT date1

FROM ....

Join (Dates)

LOAD DISTINCT date2

FROM ...

Now each date1 will have all possible date2 and each date2 will have all possible date1 available for selection.

tertre_m
New Contributor II

Re: Keep exclusive filters

Thank you for your answers but it doesn't really correspond to what I need.

I need to keep two filters at the same time that have any common row. I use set analysis but I don't know how to filter two different things to change my corresponding KPI.

My dates columns contain the same thing, i just copied it to do two different filters.

Re: Keep exclusive filters

There are few different suggestions on this page, is your response directed towards one of us?

Also, it might be easy to see the issue if you can share a sample to look at.

Best,

Sunny

tertre_m
New Contributor II

Re: Keep exclusive filters

Hi Ricardo,

Thanks for your answer.

I looked at those articles. The set analysis solutions works perfectly whenever filters correspond to overlapping data. However, as soon as I select non-overlapping date ranges (and therefore mutually exclusive filters), the first filter is automatically deleted.

I therefore doubt that the solution can only come from set analysis. What do you think, maybe variables?

robert99
Valued Contributor II

Re: Keep exclusive filters

Hi

I read this through a few times (Your first post) and I haven't a clue what you want to achieve

tertre_m
New Contributor II

Re: Keep exclusive filters

Hi Vineeth,

Thanks for the link, it seems indeed related.

However, the date ranges in this app are fixed from data load. I would like to use filters to let the user manually select date ranges.

On top of that, your linked application is quite complex for me (I am just starting with Qlik^^), hence I have trouble understanding the data load script. Do you think that the solution to my issue may lie in it? If it is the case, I will try again to work through it!

Thanks for your help

tertre_m
New Contributor II

Re: Keep exclusive filters

Hi Sunny,

I first thought that joining the dates would create a huge dataset (I have several thousand dates), but I tried and it works great, maybe thanks to a clever Qlik optimisation. Thank you for your help, it is exactly what I needed.

Best regards,

Manuel

Community Browser