Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would like to compare 2 measures "Sales Amount" and "Target Sales Amount"
Here's my load script:
Sale:
Load * inline
[
SaleID, SaleAmount, SaleYear, SaleMonth, SaleCity, SaleDay
101, 100, 2016, 1, PAR, 10
102, 10, 2016, 1, LON, 11
103, 150, 2016, 3, HEL, 9
104, 210, 2016, 4, FRA, 2
105, 40, 2016, 4, PAR, 13
106, 345, 2016, 8, LON, 21
107, 35, 2016, 8, FRA, 25
];
Target:
Load * inline
[
TargetYear, TargetMonth, TargetCity, TargetAmount
2016, 1, PAR, 20
2016, 1, LON, 30
2016, 2, PAR, 10
2016, 2, LON, 20
2016, 5, HEL, 15
2016, 6, PAR, 10
2016, 6, LON, 20
2016, 7, PAR, 15
2016, 8, PAR, 20
2016, 9, HEL, 15
2016, 10, HEL, 5
2016, 11, HEL, 0
2016, 12, PAR, 15
2016, 12, LON, 25
];
In the UI I have the following KPI:
Sale Amount = Sum(SaleAmount)
I would like to add a KPI which is the sum(TargetAmount) for TargetMonth = SaleMonth and TargetYear = SaleYear.(i.e. SaleMonth and TargetYear as selected above).
Is there a way to achieve this in the UI without changing the load script ? if not possible, what would be the best way, knowing that the filters might evolve in the future i.e. : SaleCity, SaleDay etc...
Many thanks.
Annick
Hi Annick,
Try with this expression:
=Sum({<TargetMonth=p(SaleMonth),TargetYear=p(SaleYear)>}TargetAmount)
Regards
Miguel del Valle
Hi Annick,
Try with this expression:
=Sum({<TargetMonth=p(SaleMonth),TargetYear=p(SaleYear)>}TargetAmount)
Regards
Miguel del Valle
Hi Annick
without changing the load script here is a little approch to test and adapt
KPI target amount use a set analysis as mesure like this :
Sum({$<TargetMonth={$(=num(SaleMonth))}>}TargetAmount)
it is summing the target amount depending of targetmouth when this target month is egual to SaleMonth
the same mesure adding year dimension :
Sum({$<TargetMonth={$(=num(SaleMonth))},TargetYear={$(=num(SaleYear))}>}TargetAmount)
and so on , if you want to drill down with city and day
hope it helps
regards
bruno
Thanks a lot, it works almost perfectly. I still have one requirement: is it possible to adapt this formula so that if no SaleMonth has been selected, the sum of all months is calculated? Many thanks. Annick
Please Annick, If the answer was ok, choose the correct answer and close your question.
Regards
Miguel del Valle
hi
in that case , it seems that the p formula provided by miguel should respond to your demand and is easier to use.
Hello Miguel,
I have marked your answer as correct since it gives correct result when SaleMonth is selected. However when no SaleMonth is selected, the expression does not seem to work. I will open a separate question for this. Thanks a lot for your help.
Annick