16 Replies Latest reply: Jun 15, 2018 2:56 PM by Sunny Talwar

# Alternate State Set Analysis Help

Hey everyone,

I am dealing with an issue I cant quite figure out. I have 2 alternate states (State1, State2) and I want to to find the rank with the values of State1, including the aggregated average from State2. See example below:

State 1:

IdValue
110
211
312
414
515

State 2:

IdValue
612
713
814

Result:

IdValueRank
1106
2115
3124
State 2133
4142
5151
• ###### Re: Alternate State Set Analysis Help

Would you be able to share a qvw sample to see your data? How are you getting different value for the same ids in the two states? Is it based on selections made?

• ###### Re: Alternate State Set Analysis Help

Sunny,

That was just a bad example, the values will be the same. The only thing different between the two states is the selections made.

• ###### Re: Alternate State Set Analysis Help

So, you mean different IDs will be selected?

• ###### Re: Alternate State Set Analysis Help

Yes, selections will be made to filter the IDs for each state. We want the example rank calculation based on those selections.

• ###### Re: Alternate State Set Analysis Help

One last thing... is this in Qlik Sense? I have not used Qlik Sense too much to know how to implement alternate state in Qlik Sense, I might be able to help if you can let me know how to use alternate state in Qlik Sense or I can may be help you with a solution in QlikView and you can transfer it over to Qlik Sense

• ###### Re: Alternate State Set Analysis Help

I can convert it from Qlikview to Qlik Sense.

• ###### Re: Alternate State Set Analysis Help

Check the attached image and qvw sample

• ###### Re: Alternate State Set Analysis Help

This worked great! Thank you! Can you further explain your use of "dim"?

• ###### Re: Alternate State Set Analysis Help

Just using an island table to display two parts.

For expression, when Dim = 1, show the sum of value from State 1, when Dim = 2, show the Avg of value from state 2.

For dimension, when Dim = 1, show ID as the dimension, but when Dim = 2, show some text.

• ###### Re: Alternate State Set Analysis Help

How would you add the Standard deviation of the new set, including the "State2" value?

• ###### Re: Alternate State Set Analysis Help

Sunny,

Sorry to keep this post going:

Is there any way to do this same functionality without using straight tables?

For example, if I just wanted to create a text box with the rank of the aggregated State2 values compared to the State1.

Thanks for the help!

• ###### Re: Alternate State Set Analysis Help

What would be the output look like in the text box? Can you draw a picture for me?

• ###### Re: Alternate State Set Analysis Help

Using the same data values as above, I would want to be able to to do the following without using tables:

• ###### Re: Alternate State Set Analysis Help

Getting Average value should be simple

='Average State2 Value' & Chr(10) & Avg({State2} Value)

Still trying to figure out the Rank part

• ###### Re: Alternate State Set Analysis Help

For rank, try this

='State2 Rank' & Chr(10) & RangeSum(Count(DISTINCT Aggr(If(Sum(Value) > Avg(TOTAL {State2} Value), Id), Id)), 1)