9 Replies Latest reply: Jan 27, 2014 11:39 AM by Jeremiah Kurpat RSS

    Alternate State not working

    Tan Soo Chin

      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

        • Re: Alternate State not working
          Carlos Reyes

          You were using the alternate states incorrectly.

           

          Review the expression formulas in the attached file.

            • Re: Alternate State not working
              Tan Soo Chin

              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

                • Re: Alternate State not working
                  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

                  • Re: Alternate State not working
                    Michael Laenen

                    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.

                • Re: Alternate State not working
                  Alan Farrell

                  You will need to define the Alternate State in the Expression

                   

                  2014-01-24_1456.png

                  • Re: Alternate State not working
                    Jeremiah Kurpat

                    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.reyes.qv 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!

                      • Re: Alternate State not working
                        Tan Soo Chin

                        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?

                          • Re: Alternate State not working
                            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.

                            • Re: Re: Alternate State not working
                              Jeremiah Kurpat

                              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!