Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

surynnchin
New Contributor III

Alternate State not working

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.

Capture.PNG.png

1 Solution

Accepted Solutions
jerem1234
Valued Contributor II

Re: Alternate State not working

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=$:Smiley FrustratedubProduct" 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=$:Smiley FrustratedubProduct>}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!

9 Replies
carlos_reyes_qv
Valued Contributor

Re: Alternate State not working

You were using the alternate states incorrectly.

Review the expression formulas in the attached file.

surynnchin
New Contributor III

Re: Alternate State not working

Hi

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
Honored Contributor II

Re: Alternate State not working

You will need to define the Alternate State in the Expression

2014-01-24_1456.png

carlos_reyes_qv
Valued Contributor

Re: Alternate State not working

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
Valued Contributor II

Re: Alternate State not working

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=$:Smiley FrustratedubProduct" 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=$:Smiley FrustratedubProduct>}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!

surynnchin
New Contributor III

Re: Alternate State not working

Thanks Carlos and jerem1234.

Is it instead of put sum({State1<Country=$::Country,Product=$:Smiley Tongueroduct,SubProduct=$:Smiley FrustratedubProduct>}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
Contributor III

Re: Alternate State not working

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_qv
Valued Contributor

Re: Alternate State not working

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
Valued Contributor II

Re: Re: Alternate State not working

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=$:Smiley Tongueroduct,Year={"=NullCount({State1}SubProduct)>0"}>+State1<Country=$::Country,Product=$:Smiley Tongueroduct,SubProduct={"*"}>) * $}Sales)

PFA

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

NULL handling in QlikView

Hope this helps!

Community Browser