Set Analysis Qurey

Fri, 21 Oct 2011 07:55:49 GMT

Hi,

How do i write the below IF condtion in Set Analysis.

=Sum(If(Quantity=[Invoiced Quantity],Cost_Amount,If(Quantity<>[Invoiced Quantity],Cost_Amount + [Cost Amount (Expected)])))/100000

Can any one help me.

Regards,

Antony.

Re: Set Analysis Qurey

Miguel_Angel_Baeyens — Fri, 21 Oct 2011 08:18:03 GMT

Hi Antony,

You cannot do that since the sum depends on the condition to use one field or another, actually you are using nested If(). Set analysis does not replace all kind of conditionals. Set analysis is just a way of fast filtering of data, returning a smaller subset than the set used by the whole document, that it does not seem to be the case.

I rather do that If() in the script, so you always Sum() up the same fieldand the chart will render faster:

If(Quantity = [Invoiced Quantity], Cost_Amount, If(Quantity <> [Invoiced Quantity], Cost_Amount + [Cost Amount (Expected)])) / 100000 AS CostToSum,

Being in addition your expression simpler:

Sum(CostToSum)

Further discussions on how the set analysis is not a substitute for If() and that it is evaluated once and for the whole chart can be found in this doc and in this thread that are worth reading.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Set Analysis Qurey

Fri, 21 Oct 2011 08:45:31 GMT

Hi Miguel,

Thanks for your reply.

topic Set Analysis Qurey in QlikView

Set Analysis Qurey

Re: Set Analysis Qurey

Set Analysis Qurey