Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

KPI : Link and compare 2 measures with different dimensions

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

1 Solution

Accepted Solutions
migueldelval
Specialist
Specialist

Hi Annick,

Try with this expression:

=Sum({<TargetMonth=p(SaleMonth),TargetYear=p(SaleYear)>}TargetAmount)

Regards

Miguel del Valle

View solution in original post

6 Replies
migueldelval
Specialist
Specialist

Hi Annick,

Try with this expression:

=Sum({<TargetMonth=p(SaleMonth),TargetYear=p(SaleYear)>}TargetAmount)

Regards

Miguel del Valle

brunobertels
Master
Master

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

Not applicable
Author

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

migueldelval
Specialist
Specialist

Please Annick, If the answer was ok, choose the correct answer and close your question.

Regards

Miguel del Valle

brunobertels
Master
Master

hi

in that case , it seems that the p formula provided by miguel should respond to your demand and is easier to use.

Not applicable
Author

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