11 Replies Latest reply: Apr 28, 2009 6:48 PM by bcolgan85 RSS

    set analysis and if statements!

      Hi guys,


      Apologies, but this is a difficult one to try and explain, and I am unable to send the application.

      I have 2 variables: vmonthlyuptime and system choice.

      each variable is a very long equation with set analysis implemented so that if a date is selected it will not affect the charts on the dashboard:

      The set analysis is correct. However when I try and combine the variables into an if statement the set analysis does not appear to work:

      My equation is:


      if(getcurrentfield(Time)='MonthYear', (if(UnitDesc='Debt and Treasury Technology', $(TomsSystemChoice), $(vMonthlyUptime))), (if(UnitDesc='Debt and Treasury Technology', $(TomsSystemsWeekly),$(vWeeklyUptime))))

      It says the expression is correct, but when a date is selected the figures change which is not what I want.


      Could anyone think of something that may be wrong with this? I'm really at a loss as to see what is wrong.


      Thanks in advance,










        • set analysis and if statements!
          Oleg Troyansky

          I can't see anything that would be obviously wrong with this expression, so the only thing I can offer is some "debugging" directorions:

          1. What if you try and add 4 independent expressions with the 4 variables with no IF statements and see if they behave like you expected.

          2. With complex expressions, like this one, the Expression syntax checker is usually getting "confused" and it stops identifying errors - any expression appears as "OK".

          3. Try to find a way to simplify your expression - after all, you have 2 nested IF statements, with 4 complex expressions with Set Analysis... I wouldn't be surprised if QlikView is getting "confused" with such a complex structure... You can easily avoid at least one "IF" - read my Wiki about using flags. This is perhaps another case of using Flags:

          Instead of

          if(UnitDesc='Debt and Treasury Technology', <expr1>, <expr2>),

          you can calculate a flag - let's call it DTT_Flag that will have 1 when your condition is true and 0 if it's false. Then, your expression could look like:

          <Expr1>* DTT_Flag + <Expr2>*(1-DTT_Flag)

          Good luck!



            • set analysis and if statements!

              Thanks Oleg,


              Could you post the link to the wiki page? I'll try and implement it if I can.





                  • set analysis and if statements!

                    Hi Oleg,


                    I understand what way the flags work but I'm not sure what way I should go about trying to create one as I have numerous nested fields?

                    if(getcurrentfield(Time)='MonthYear', (if(UnitDesc='Debt and Treasury Technology', $(TomsSystemChoice), $(vMonthlyUptime))),


                    (if(UnitDesc='Debt and Treasury Technology', $(TomsSystemsWeekly),$(vWeeklyUptime))))*/

                    The more I look at the expression, the more confused I get!



                      • set analysis and if statements!
                        Oleg Troyansky

                        I'm not sure what's causing the problem in your case. It's hard to guess without looking at the whole picture.

                        • If anything else looks absolutely accurate,
                        • and all 4 individual expressions without IF statements work as expected,
                        • and they only stop working when combined with IF statements

                        then the logical conclusion is that QlikView didn't like the combination of nested IFs with the Set Analysis expressions inside. If that's the conclusion (again, I'm not sure it is because I can't see your app), - then you can at least avoid one IF by replacing it with the flag:

                        • In your load script, in the table that holds UnitDesc, add a flag as following:

                        IF(UnitDesc = 'Debt and Treasury Technology' , 1, 0) as DTT_Flag

                        • In your expression, replace the IF by the Flag, like this:


                        DTT_Flag * ( $(TomsSystemChoice) ) + (1-DTT_Flag) * ( $(vMonthlyUptime)),

                        DTT_Flag * $(TomsSystemsWeekly) + (1-DTT_Flag) * ($(vWeeklyUptime))


                        Hope it helps,


                          • set analysis and if statements!

                            Thanks Oleg,

                            This does indeed work but the set analysis does not seem to work, ie when a date is selected only one column shows..I would like this to show the same figures regardless of date selection (as it is a trending graph over time).

                            If I could get this working it would be perfect!

                            Any more thoughts would be much appreciated :)


                  • set analysis and if statements!
                    Rob Wunderlich

                    Could the problem be due to when the formula in the variable is getting calculated? If you paste the variabl formula directly into the expression, does it work correctly?


                      • set analysis and if statements!

                        Hi Rob,

                        It's strange because I broke the problem down into parts and it seemed to work.

                        The graph I had, had an option on the x-axis to switch between a monthly view or a view over the past twelve weeks. I can create two separate graphs where one has the monthly view and one has the weekly view. Each works and is unaffected by date selection(set analysis working). However, when I use the if condition so that the two graphs work as one, the set analysis doesn't work.

                        I'm at a loss as to why this is happening....Any other thoughts?

                          • set analysis and if statements!
                            Jay Jakosky

                            Do you have an example file you can upload?

                              • set analysis and if statements!

                                Hi Jay,

                                The expressions are really complex and for security reasons I cannot send the app scrambled/unscarambled.

                                An example of the variable would be :









                                -(sum({$<MonthYear= { * }, IncidentId = { * }, Day = { * }, Week = { * }, StartDate = { * }>} sevimpact)/

                                (networkdays(monthstart(min({$<MonthYear= { * }, IncidentId = { * }, Day = { * }, Week = { * }, StartDate = { * }>}StartDate)),monthend(max({$<MonthYear= { * }, IncidentId = { * }, Day = { * },Week = { * }, StartDate = { * }>}StartDate)))*60*24)/sum({$<MonthYear= { * }, IncidentId = { * }, Day = { * }, Week = { * }, StartDate = { * }>}total Flag))

                          • set analysis and if statements!


                            I have decided for the meantime to produce two separate graphs, one for month trending and another showing the last 12 weeks.

                            Having a little trouble with the background colour format though.

                            Basically I want this to incorporate both variables into the colour format, ie if vmonthlyuptime or vmonthlytomuptime >= 0.9995, rgb, (51, 204, 102), and so forth. However, the equation I have below only works for one part of the equation. Apologies for bombaring you with all these questions!




                            if($(vMonthlyUptime)>=0.9995, rgb(51,204,102),






















                            if($(vMonthlyUptime)<0.9995 and $(vMonthlyUptime)>=0.9990, rgb(255,128,0),








                            if($(vMonthlyTomUptime)>=0.9995, rgb(51,204,102),




                            if($(vMonthlyTomUptime)<0.9995 and $(vMonthlyTomUptime)>=0.9990, rgb(255,128,0),






                            I really appreciate any help given!