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

    Set analisis + if

    Iñigo Elviro Bodoy



      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:




      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))




          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:





          - Marcus