Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have requirement as pasted below
I should get one Exchange rate as one column/Filter in front end with filter option as Exch Rate1,Exch Rate2,Exch Rate3
My value column should calculate based on filter selected from Exchange rate , For ex Value*Exch Rate1 when I select Exch Rate1 from filter and Value*Exch Rate2 when I select Exch Rate2 so on.
Month | Brand | Actual | Budget | Exch Rate1 | Exch Rate2 | Exch Rate3 |
Jan20 | AA | 56 | 70 | 0.2 | 0.4 | 0.7 |
Feb20 | BB | 60 | 75 | 0.3 | 0.5 | 0.6 |
Mar20 | CC | 75 | 80 | 0.1 | 0.2 | 0.3 |
You need to transform the data a bit:
Data:
LOAD * INLINE [
Month|Brand|Actual|Budget|Exch Rate1|Exch Rate2|Exch Rate3
Jan20|AA|56|70|0.2|0.4|0.7
Feb20|BB|60|75|0.3|0.5|0.6
Mar20|CC|75|80|0.1|0.2|0.3
]
(DELIMITER IS '|');
Table_1:
LOAD
Month,
Brand,
Actual,
Budget
RESIDENT Data;
Table_2:
CROSSTABLE(Exchange_Rate,Value) LOAD
Month,
[Exch Rate1],
[Exch Rate2],
[Exch Rate3]
RESIDENT Data;
DROP TABLE Data;
Then you just need a straight table and a filter on the front end: