Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am applying filter with Alternate states (Compare 1 & Compare 2) on two different Filters which works fine. But how to apply the filter with alternate states when we land on that sheet.
I want to show the Top 2 counties with the highest sales.
MyTable:
Load * Inline [
Country, Year, Sales
USA, 2014, 66295.03
Argentina, 2015, 140037.89
China, 2014, 54166.09
Austria, 2015, 182739.87
India, 2017, 526838.97
];
As per the above Inline load, India and Austria has the highest sales. These 2 counties must be filtered with alternate state 'Compare 1' and 'Compare 2' when we land on the sheet.
In the above image I have filtered them manually.
When I Apply the logic for highest sales country name in the Sheet action. It works but goes in default state.
Thanks in Advance.
Hello !
This is possible thanks to bookmarks.
In the filter pane assigned to compare report 1, make a selection using this formula:
=[Country]=firstsortedvalue(TOTAL Country,-aggr(Sum(Sales),Country),1)
Once the formula has been cruised, and the formula has been placed on entry in order to do the search, India will be selected from this formula.
Similarly, in the filter pane assigned to the compare state 2, effect a selection using this formula:
=[Country]=firstsortedvalue(TOTAL Country,-aggr(Sum(Sales),Country),2)
Then, create a bookmark. Assign this bookmark to open a sheet.
Enjoy your Qlik.
Kind regards,
Théo ATRAGIE.
Hi Buddy,
Thanks for the reply, the logic works fine for the 1st one:
=[Country]=firstsortedvalue(TOTAL Country,-aggr(Sum(Sales),Country),1)
When the selection is made, I tried the 2nd expression
=[Country]=firstsortedvalue(TOTAL Country,-aggr(Sum(Sales),Country),2)
But it was unable to search the value. The 2nd expression works fine only when there is no selection as per the 1st Expression.
This is because, I wanted to have Top 2 countries as per the sales. It works with the different alternate states. But within the same alternate states. I can't select two values at the same time.
Please refer to the screenshot below for better view.