topic Re: Set Analysis and IF in QlikView

Branching and not able to use set analysis

Wed, 18 Sep 2013 13:36:01 GMT

Hi i have posted a previous message asking about set analysis and IF statements;

in the case i post below i would like to build the knowledge whereby i can use set analysis for two potential outcomes based on the IF condition

example:

IF(OrderRowRab = 'N',

sum([sellingprice] * [xchange] * [total]),

sum([sellingprice] * [xchange] * [total]) * (1+ headerdisc) * (1+rowdiscount))

i would like to make this a set analysis statement or find another way that i can aggregate the data onto a date dimension ie. Year or month or even Order.

as you see in the example it is replication the ordernummer 5 times(depending on the look up on the Row for the value 'N').

i would like it to show the ordernummer and the aggregate only not repeating.

any help is greatly appreciated

Best,

Bradley

Re: Set Analysis and IF

Gysbert_Wassenaar — Wed, 18 Sep 2013 13:58:48 GMT

It's not possible to rewrite this as a set analysis expression. A set analysis expression calculates one set for the entire chart to filter the values used in the calculation of the expression. It cannot be used for execution branching.

Re: Set Analysis and IF

Wed, 18 Sep 2013 14:16:54 GMT

Thank you for your explanation.

is there a potential way to express this without including all the fields in a table?

Best,

Bradley

Re: Branching and not able to use set analysis

Wed, 10 Aug 2016 23:21:20 GMT

There is a workaround. You can create two columns each one with one Set expression then you set them to Conditional display = 0 and you create a third column where you have your test. i.e.
If(Isnull(Test), Column1, Column2)

Re: Branching and not able to use set analysis

sunny_talwar — Thu, 11 Aug 2016 10:28:26 GMT

This might be a set analysis alternative to your if statement:

RangeSum(

Sum({<Radnummer = {"=Len(Trim(OrderRad_Ej_OHRabatt)) > 0"}>}If(IsNull([Förs-kurs]),1,[Förs-kurs])),

Sum({<Radnummer = {"=Len(Trim(OrderRad_Ej_OHRabatt)) = 0"}>}If(IsNull([Förs-kurs]),1,[Förs-kurs])) *

(1 + Only({<Radnummer = {"=Len(Trim(OrderRad_Ej_OHRabatt)) = 0"}>}OrderRad_Rabatt)) * (1+Only({<Radnummer = {"=Len(Trim(OrderRad_Ej_OHRabatt)) = 0"}>}OrderHuvud_Rabatt)))

* [Förs pris] *[Ursprungligt antal]