1 Reply Latest reply: Mar 21, 2015 10:40 AM by Marcus Sommer RSS

    Set analisis + if

    Iñigo Elviro Bodoy

      Hi:

       

      I would like to filter one expression with if, but without changin the variable.

       

      I have one variable:

       

      v%Waste= Sum(Aggr(Sum ({<TIPO={"Waste"}>}Quantity),Day,Supplier))/

      Sum(Aggr(Sum ({<TIPO={"Consumes"}>}Quantity),Day,Supplier))

       

      And the expression to filter is:

       

      =If(10-($(v%Waste)-0.01)*1000>10,10,If(10-($(v%Waste)-0.01)*1000<0,0,10-($(v%Waste)-0.01)*1000))

       

      It works well alone but when I tried this expression filtering by day>31/12/2014 and < actual month doesn´t work:

       

      Sum ({<Day= {">31/12/2014"},MonthYear={"<Date(MonthStart(Today()),'MMM-YY'))"}>} $(v%Waste))

       

      Could somebody help me?

       

      I have the personal edition, therefore I can not open qlikview examples. Please, send me the corrections in the formuls.

       

      Thanks in advance.

        • Re: Set analisis + if
          Marcus Sommer

          This couldn't work because it is a nested aggregation without an aggr. You need to add the aggr or to put the extra condition inside the variable-expression:

           

          Sum ({<Day= {">31/12/2014"},MonthYear={"<Date(MonthStart(Today()),'MMM-YY'))"}>} aggr($(v%Waste)Day,Supplier))

           

          or

           

          Sum(Aggr(Sum ({<TIPO={"Waste"},

               Day= {">31/12/2014"},MonthYear={"<Date(MonthStart(Today()),'MMM-YY'))"}>}Quantity),Day,Supplier)) /

          Sum(Aggr(Sum ({<TIPO={"Consumes"},

               Day= {">31/12/2014"},MonthYear={"<Date(MonthStart(Today()),'MMM-YY'))"}>}Quantity),Day,Supplier))

           

          Further the extra condition looks a little bit strange. It compared a day with a date and a period with a date - I think it should more look like:

           

          ... Date= {">'31/12/2014'<$(=Date(MonthStart(Today()),'DD/MM/YYYY'))"}>} ...

           

          and if it's a single expression to use a variable for this adds no advantages. If you used this kind of expression often you could flexibilize such expression-variables with parameter:

           

          http://community.qlik.com/message/617782#617782

          http://community.qlik.com/message/610606#610606

           

          - Marcus