Aggregation with isnull and if

[#1]

Hi,

I am having troubles trying to do some calculations, I hope someone could help me with it.

I'm developing a table which shows Sales and another amounts like Fares and calculations between them The thing is if an order has a Fare then I want to show it and sum it, if not then I want to use the Sales amount instead.

The following is an example of the expected result:

Region	Sales	Fare	Fare1-Sales
A	100	160	60
B	150	275	125
C	250	365	115

This is the expression I'm using:

IF(

    ISNULL(AGGR(  sum(Fare1), Order, Region))

    ,   

    sum(Sales)

    ,

    AGGR(  sum(Fare1), Order, Region))

)

The problem is that is showing the same amounts as the Sales, looks like the IF is not applying at order level.

The datamodel is based on these tables, I've simplified it because I've seen other tables does not affect.

- Orders: Order, Sales, Region

- Fares: Order, Fare1, Fare2...

Any idea?

Thanks in advance.

[MK_QSL]

Can you provide 5 lines sample/dummy data?

[#3]

Hi,

I've attached a very simple example, but it shows 2 differences from the original one,

- The example does not compare well using the isnull function, but it works comparing the aggr to 0.

- The example does not show the sales amounts on the calculated fields even when there is no fare.

In the table by Region I've tried different combinations using the AGGR without luck.

Many thanks

[MK_QSL]

In expression, you have to consider Null value as 0.

Also, there is no value associated with Order 2 and 5 so even if you uses IsNull(SUM(Sales)) you will get False.

So the method you have used as 0 is the right approach.

[#5]

I'd like to get the following table per Region:

Region	Sales	Calc
A	75	85
B	95	125
C	60	60

A = 60 + 25

B = 85 + 40

C = 60

So, if I do something like this:

IF ( AGGR( sum(Fare1), Order)=0

    ,

    sum(Sales)

    ,

     sum(Fare1)

  )

It gives me the sum(Fare1) for orders 1,3 and 4 but it does not sum the Sales for order 2 Why?

[MK_QSL · Accepted solution]

Ok...

Use Straight Table

Dimension

Region

Expression

SUM(Sales)

SUM(Aggr(IF(SUM(Fare1) = 0, SUM(Sales),SUM(Fare1)),Order, Region))

[ramoncova06]

you do not have the order field in you table, QV will not take into account and will calculate the formula as a whole, so if one order has a 0/null it will consider as the whole region as a null

use rangesum instead  and apply a search in your set analysis

RangeSum(

sum({<Order = {"=Sum(Fare1) = 0"}>}Sales),sum({<Order = {"=Sum(Fare1) <> 0"}>}Fare1))

[#8]

Hi guys,

Both solutions solved the problem!

Ramon I guess it works because it is aggregated at Order level and Region level as well.

Thanks both!