Set Analysis Qurey

[#1]

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.

[Miguel_Angel_Baeyens]

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

[#3]

Hi Miguel,

            Thanks for your reply.