Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Ok...
Use Straight Table
Dimension
Region
Expression
SUM(Sales)
SUM(Aggr(IF(SUM(Fare1) = 0, SUM(Sales),SUM(Fare1)),Order, Region))
Can you provide 5 lines sample/dummy data?
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
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.
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?
Ok...
Use Straight Table
Dimension
Region
Expression
SUM(Sales)
SUM(Aggr(IF(SUM(Fare1) = 0, SUM(Sales),SUM(Fare1)),Order, Region))
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))
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!