Alternate State not working

[Anonymous]

Hi Community

I'm having issue with alternate state. I have created 2 alternate states: State1 and State2, i want to make a year to year comparison.

Why my table showing nothing when i select Year 2010 and Year 2011? Anything goes wrong with my expressions? Thanks in advance.

[Carlos_Reyes]

You were using the alternate states incorrectly.

Review the expression formulas in the attached file.

[Anonymous]

Hi Carlos

Thanks for reply. Could you explain more what's wrong with my expression? I'm confuse now. I get my expression from here community.qlik.com/docs/DOC-3837

And according to the author

if I'd like to reuse the "default" data association such as Year, Month or any other common fields, then I can use them for the alternate state groups by using $::. ?

Regards

SurynnChin

[rustyfishbones]

You will need to define the Alternate State in the Expression

[Carlos_Reyes]

Surynn,

I'm no expert in Alternate States, but for what I've learned, you just have the possibility to retrieve selections from another set of information so it's just a matter of mixing things up to get your desired result... easy uh!

I've seen the document by Shima and also reviewed the What's New in QlikView 11 demo, and your expression should work! I mean, they use the same sintax that you're using... so in short... I don't know what's the problem. The only thing I've realized is that f you select Years different than 2010 and 2011 your expressions do work... Why? I don't know. But... have you tried to save and reload the app? Perhaps there is some problem with the Alternate States and reloading the document could refresh the sets... That's all I can think of for now.

Regards

[jerem1234 · Accepted solution]

The problem is here not your formula for alternate states, but with the fact that your data for 2010 and 2011 have nulls for subproduct. Therefore when you use "SubProduct=$::SubProduct" in your formula, it only gives you the sum for which there is a subproduct for. You can test it out yourself using the regular default state using the formula and selecting 2010 or 2011 in the default state:

sum({<SubProduct=$::SubProduct>}Sales)

This will give you same problem as the State1 state does.

The way Carlos Alberto Reyes Díaz mentions gets around this since SubProduct is not specified in the set analysis (and is cleaner since you are only specifying one field for State1). Therefore you are not excluding the nulls.

Hope this helps!

[Anonymous]

Thanks Carlos and jerem1234.

Is it instead of put sum({State1<Country=$::Country,Product=$::Product,SubProduct=$::SubProduct>}Sales), i should put sum({<Year=State1::Year>}Sales), if there are null in any other common fields which I'd like to reuse the "default" data association? And latter is the safest expression to put as sometimes it would be hard for us to check each of the field in our data whether the field contains null or not?

[agilos_mla]

In your case, it is also possible can also use the intersection operator in the set analysis:

sum({State1*$} Sales)     : give the result expected if you don't have a LB Year in the default state, if any,

sum({State1*$<Year=>} Sales    : will disregard the year in the default state but will consider other selections.

Sometimes using the $:: syntax can be very heavy then get back to simple things ;-))

Michael.

[Carlos_Reyes]

I think it's your best option... in fact I always do that way and that's what made me think that your formula was incorrect in the first place.. I always use the default set and then define a different set for those fields that come from a different one... It makes more sense to my logic... although I didn't know that this method was saving me from the problem with nulls...

Thanks to jerem1234 for enlighten us about this behavior with alternate states.

[jerem1234]

Yes for your situation, it is the safest bet. Seems like null handling becomes a little tricky when it comes down to alternate states. Out of curiosity, I managed to get  the way your original way was doing alternate states to work using:

sum({(State1<Country=$::Country,Product=$::Product,Year={"=NullCount({State1}SubProduct)>0"}>+State1<Country=$::Country,Product=$::Product,SubProduct={"*"}>) * $}Sales)

PFA

Also here might be a helpful guide to handling nulls in Qlikview by HIC:

NULL handling in QlikView

Hope this helps!