7 Replies Latest reply: Aug 26, 2014 9:41 AM by Brian Nash RSS

    Help with Set Analysis

    Brian Nash

      I have the following IF statement but the test always fails and I only get the "false" side of the expression on every use. I should, depending on the project's projected closing date, return a "True" but it only works when I select a project or projects. In other words, this statement always fails the first IF statement when no projects are selected. If I select a project that has a  Proj Closing Date of next year the statement returns "True", If I don't select this project it is "False". Any help much appreciated.....Thanks

       

      =IF(Year([Proj Closing Date])> Year(vMaxDate),

      SUM({$<Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} IF(Year([Proj Closing Date])> Year(vMaxDate), [CY Fee Budget], [Fee Budget]* [Probability of Closing]))

      -

      SUM({$<Year= , Year ={"$(vMaxYear)"},  [Deal Status]= >} Revenue),

       

      SUM({$<Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC","Won Pitch"}>} IF(Year([Proj Closing Date])> Year(vMaxDate), [CY Fee Budget], [Fee Budget]* [Probability of Closing]))

      -

      SUM({$<Year= , Month= , YearMonth= , Date= ,Date = {"<=$(vMaxDate)"}, [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} Revenue* [Probability of Closing]))

        • Re: Help with Set Analysis
          Phaneendra Kunche

          i believe this to work you have to have Proj Closing Date as dimension in the chart. Because in qlikview if that field is not in grid/chart it wont do a row wise computation.

           

          lets say you have 3 years in Proj Closing Date, behind the seen qlikview will be taking all 3.

          In oyur case when you select one project you are getting one year only and that simplifies for IF and you are seeing the result.

           

          Best example: Add "=Year(Proj Close Date)" in a text box and clear everything. you will see null "-". This is what is happening in your scenario when you dont select a project.

           

          Can you try adding Proj Closing Date a dimension? or if you dont want you have to go with Aggr statement.

          • Re: Help with Set Analysis
            Brian Nash

            I understand exactly what you're talking about. And yes the test for the chart does come back null. I do have the proj close date as a dimension in my chart and in fact each line in the table brings back the proper result of the expression. The problem I have is the expression as a total for the chart is incorrect. I just don't know how to get the expression in total to work.

            • Re: Help with Set Analysis
              Brian Nash

              I wish I could but there is too much confidential data within.

              • Re: Help with Set Analysis
                Brian Nash

                I still don't understand how to get the initial if statement to return True

                • Re: Help with Set Analysis
                  jagan mohan rao appala

                  Hi Brian,

                   

                  Try like this

                   

                  =IF(Year(Max([Proj Closing Date]))> Year(vMaxDate),

                  SUM({$<Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} [CY Fee Budget])

                  -

                  SUM({$<Year= , Year ={"$(vMaxYear)"},  [Deal Status]= >} Revenue),

                   

                  SUM({$<Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC","Won Pitch"}>} [Fee Budget]* [Probability of Closing])

                  -

                  SUM({$<Year= , Month= , YearMonth= , Date= ,Date = {"<=$(vMaxDate)"}, [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} Revenue* [Probability of Closing]))

                   

                  Hope this helps you.

                   

                  Regards,

                  Jagan.

                  • Re: Help with Set Analysis
                    Brian Nash

                    Thanks. I tried but came up with incorrect expression total again. Ended up modifying it to this and it appears to work:

                    SUM({$<[Proj Closing Date]={">2014-12-31"},Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>}  [CY Fee Budget]*[Probability of Closing])

                    -

                    SUM({$<[Proj Closing Date]={">2014-12-31"},Year= ,  Year ={"$(vMaxYear)"},  [Deal Status]= >} Revenue)

                    +

                    SUM({$<[Proj Closing Date]={"<=2014-12-31"},Year= , Month= , YearMonth= , Date= , [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC","Won Pitch"}>}  [Fee Budget]* [Probability of Closing])

                    -

                    SUM({$<[Proj Closing Date]={"<=2014-12-31"},Year= , Month= , YearMonth= , Date= ,Date = {"<=$(vMaxDate)"}, [Deal Status]={"In Progress", "Approved Pending Further Review", "Pitch/RFP", "On Hold", "CVC", "Won Pitch"}>} Revenue* [Probability of Closing])