topic Re: Aggregation with isnull and if in QlikView

Aggregation with isnull and if

Tue, 23 Jun 2015 11:33:26 GMT

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.

Re: Aggregation with isnull and if

MK_QSL — Tue, 23 Jun 2015 11:35:02 GMT

Can you provide 5 lines sample/dummy data?

Re: Aggregation with isnull and if

Tue, 23 Jun 2015 14:45:19 GMT

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

Re: Aggregation with isnull and if

MK_QSL — Tue, 23 Jun 2015 15:11:17 GMT

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.

Re: Aggregation with isnull and if

Tue, 23 Jun 2015 15:21:04 GMT

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?

Re: Aggregation with isnull and if

MK_QSL — Tue, 23 Jun 2015 15:24:49 GMT

Ok...

Use Straight Table

Dimension

Region

Expression

SUM(Sales)

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

Re: Aggregation with isnull and if

ramoncova06 — Tue, 23 Jun 2015 15:25:23 GMT

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))

Re: Aggregation with isnull and if

Thu, 25 Jun 2015 10:13:42 GMT

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!