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

# Set analisis + if

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.

• ###### Re: Set analisis + if

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