topic Set Analysis For Deminsions Selected From Input Box in QlikView

Set Analysis For Deminsions Selected From Input Box

kevin_creese — Sat, 16 Nov 2024 21:29:41 GMT

Hello Community!

I have a business case where we want to display pie charts based off of inputs that the user selects. For context, they want to be able to toggle values between sales and units as expression values. The issue I have is that in order to display pie charts you must have positive values, unfortunately, the dataset we are using will also reflect negative values (e.g. credits/returns).

If I use a standard set analysis I can get past this. Here is a static example:

=SUM({<[Sales Amt] = {'>0'},[Ship_to State] = {'OR'}>}[Sales Amt])

In the above example, the expression that they want to toggle between is [Sales Amt].

I'm using an input box with a list constraint to select [Sales Amt] or [Units] and assign it to the variable vMeasure1.

I can't figure out how to modify my set analysis to recognize my first set analysis ([Sales Amt] = {'>0'}) with a variable input. I was thinking something like:

=SUM({<$(vMeasure1) = {'>0'},[Ship_to State] = {'OR'}>}$(vMeasure1)) which results in "Error in expression".

What am I missing here?

Re: Set Analysis For Deminsions Selected From Input Box

dplr-rn — Thu, 31 Jan 2019 16:07:13 GMT

Hi
havent used your way but during testing i am getting inconsistent results when i emulate your method (it works but when i compare against actual values its not correct; i am sure there will be theoritical reason why)
but below works correctly
Sum( {<$(vSetVariable)>} $(vSetAnalysis2))
basically vSetVariable contains the entire set analysis expression string. vSetAnalysis2 contains the measure column
Hope it helps

Re: Set Analysis For Deminsions Selected From Input Box

dplr-rn — Thu, 31 Jan 2019 16:15:12 GMT

Actually managed to get below working

Sum( {<$(vSetVariable3)={"<=10 >=2"}>} $(vSetAnalysisVariable2))
Did you check output of your variable?

Re: Set Analysis For Deminsions Selected From Input Box

kevin_creese — Thu, 31 Jan 2019 16:34:26 GMT

Dilipranjith,

Thanks for the reply. I think I have figured out a work around. I attached a more detailed version of what I'm trying to do. Take a look at the QVW file that I threw together with a simple inline data set.

I think I will have to utilize an if statement to confirm that the value I want to see is positive and then I can remove it from my set analysis completely. This seems to work, but if anyone has a better way of doing this I would be happy to explore other options.

I was trying to use the value from one variable to determine a value in the dimension as well as in the measure.

Re: Set Analysis For Deminsions Selected From Input Box

dplr-rn — Thu, 31 Jan 2019 18:24:58 GMT

Ah.. you need the sum. yes that maybe the best case approach. Only alternative i can think of is an aggr function inside the set. but no idea how that works with a variable