Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello guys, i have the following question:
Imagine you have the three following tables.
A:
Load * Inline
[
Nation , Client
A , One
B , Two
];
B:
Load * Inline
[
Client , Date , Revenue
One , 1 , 10
One , 2 , 20
One , 3 , 30
Two , 1 , 11
Two , 2 , 11
];
C:
Load * Inline
[
Nation , Date2 , Revenue2
A , 1 , 20
A , 2 , 20
A , 3 , 20
B , 1 , 20
B , 2 , 20
B , 3 , 20
B , 4 , 20
];
I want to make a bar chart with dimension = date and measure should be a combination of table B and C. For example, the bar on date = 1 should be like 21 (from table B) + 40 (from table C).
If i select for example Nation = 'A', then of course the bar should be for Date = 1
10 (from table B) + 20 (from table C) = 30.
I tried something like
Sum(Revenue) + if(Date = Date2, Sum(Revenue2))
but it doesnt work.
Hi
Try like below
MapClient:
Mapping
Load * Inline
[
Nation , Client
A , One
B , Two
];
MapNation:
Mapping
Load Client, Nation Inline
[
Nation , Client
A , One
B , Two
];
B:
Load *, ApplyMap('MapNation', Client) as Nation Inline
[
Client , Date , Revenue
One , 1 , 10
One , 2 , 20
One , 3 , 30
Two , 1 , 11
Two , 2 , 11
];
C:
Load Nation, Date2 as Date, Revenue2 as Revenue, ApplyMap('MapClient', Nation) as Client Inline
[
Nation , Date2 , Revenue2
A , 1 , 20
A , 2 , 20
A , 3 , 20
B , 1 , 20
B , 2 , 20
B , 3 , 20
B , 4 , 20
];
Hi @DorryD ,
change your script a bit like following:
A:
Load Nation&Client as %DimKey, Nation, Client Inline
[
Nation , Client
A , One
B , Two
];
B:
Load * Inline
[
Client , Date , Revenue
One , 1 , 10
One , 2 , 20
One , 3 , 30
Two , 1 , 11
Two , 2 , 11
];
outer join(B)
C:
Load Nation, Date2 as Date, Revenue2 Inline
[
Nation , Date2 , Revenue2
A , 1 , 20
A , 2 , 20
A , 3 , 20
B , 1 , 20
B , 2 , 20
B , 3 , 20
B , 4 , 20
];
NoConcatenate
FACT:
Load
Nation&Client as %DimKey,
Date,
rangesum(Revenue,Revenue2) as Revenue
Resident B;
drop table B;
Use simple expression sum(Revenue) by any dimension, you like and it should be working.
BR
m
Hi DorryD,
Try this:
Sum(Revenue) + sum(if(Date = Date2, Revenue2))
Hi Mayil Vahanan R,
this example is a simplification of my problem. Your approach works, but i'm searching for a solution where i don't have to change my data modell.
Hi Mato,
this example is a simplification of my problem. Your approach works, but i'm searching for a solution where i don't have to change my data modell.
Hi Simsa,
thanks for your help but unfortunately this solution doesn't work if you have more than one client per nation.