Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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.
Dear Manuel,
Take a look on these articles:
Set analysis – an introduction ‒ Qlik Sense
Set analysis – taking the next step ‒ Qlik Sense
Period Presets: Compare Periods on the fly
You can compare overlapping periods like this
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.
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.
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
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?
Hi
I read this through a few times (Your first post) and I haven't a clue what you want to achieve
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
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