Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to not show data in a table if one of several filters are selected and I figured the best place to enter a formula would be in the Add-ons>>>>Data handling>>>>Calculation condition section of the table I want to quit showing data for.
I tried to utilize nested if statements as follows:
IF(GetSelectedCount(Filter1)<> 0,0
,IF(GetSelectedCount(Filter2)<>0,0
,1))
But it does not seem to work with 2 conditions being checked
But the single version works fine IF(GetSelectedCount(Filter1)<> 0,0,1) if I select Filter1 the Data Ahdnling works as expected.
I also tried multiple single conditions tied together with an AND operator and an OR operator as follows:
IF(GetSelectedCount(Filter1)<> 0,0,1) or IF(GetSelectedCount(Filter2)<>0,0,1)
IF(GetSelectedCount(Filter1)<> 0,0,1) and IF(GetSelectedCount(Filter2)<>0,0,1)
But neither of them worked as expected, Is it possible to do nested ifs in the Data Handling area?
Rob thanks for the reply I could not get the filtering to work without the IF statement but I did figure out why 3 of the 5 filters did not work. When I looked closely at what was and what was not working I noticed that the three that were not working all had additional code in the filter for example I wanted AppVersion which would by default say 0.5.3 or 1.0.0 to actually say App Ver: 0.5.3 and App Ver: 1.0.0 so I added the following in the filter 'App Ver: '&AppVersion and I guess that is an issue. I got around this by adding that to the load script as opposed to the filter. I also had to remove the filter pane from every additional sheet while I was working on this issue leaving the filter pane only on one sheet as it also appears that Qlik Sense Enterprise looks at every filter pane so when I made these changes on the one to test it did not work because all the other filter panes had the original values.
So by removing all the filter panes, modifying the load script to add in the additional text for clarification, and using the following in the calculation filed everything is now working:
IF(GetSelectedCount([UserType])<>0
or GetSelectedCount([OperatingSystemVersion])<>0
or GetSelectedCount([Country])<>0
or GetSelectedCount([AppVersion])<>0
or GetSelectedCount([MobileDeviceInfo])<>0
,0,1)
Thanks for your reply this was a difficult issue to trouble shoot I had to step away for awhile and think about it and once I did it now makes sense to me as to why it was acting the way it was.
I'm not sure why you are not getting counts for some fields, but I can offer some suggestions to make your overall syntax simpler.
The calculation resolves to a true/false, and the object is calculated if true. You do not need the if() function. You can simply write:
GetSelectedCount(Filter1) > 0
In Qlik 0 is false and everything else is true. So you could simplify even further as:
GetSelectedCount(Filter1)
As you discovered, you can test multiple fields (or conditions) using OR. So write:
GetSelectedCount(Filter1)
or GetSelectedCount(Filter2)
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Rob thanks for the reply I could not get the filtering to work without the IF statement but I did figure out why 3 of the 5 filters did not work. When I looked closely at what was and what was not working I noticed that the three that were not working all had additional code in the filter for example I wanted AppVersion which would by default say 0.5.3 or 1.0.0 to actually say App Ver: 0.5.3 and App Ver: 1.0.0 so I added the following in the filter 'App Ver: '&AppVersion and I guess that is an issue. I got around this by adding that to the load script as opposed to the filter. I also had to remove the filter pane from every additional sheet while I was working on this issue leaving the filter pane only on one sheet as it also appears that Qlik Sense Enterprise looks at every filter pane so when I made these changes on the one to test it did not work because all the other filter panes had the original values.
So by removing all the filter panes, modifying the load script to add in the additional text for clarification, and using the following in the calculation filed everything is now working:
IF(GetSelectedCount([UserType])<>0
or GetSelectedCount([OperatingSystemVersion])<>0
or GetSelectedCount([Country])<>0
or GetSelectedCount([AppVersion])<>0
or GetSelectedCount([MobileDeviceInfo])<>0
,0,1)
Thanks for your reply this was a difficult issue to trouble shoot I had to step away for awhile and think about it and once I did it now makes sense to me as to why it was acting the way it was.