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: 
raajeshn
Partner - Creator
Partner - Creator

Help with Set Expression

Hi Experts,

Request your help on the below scenario. Data set being used is as follows.

LOAD * INLINE [

    KPI, Year, PKey, Value

    Actual, 2017, 1, 30

    Actual, 2016, 1, 40

    Actual, 2014, 1, 50

    Benchmark, , 1, 25

    Actual, 2017, 2, 40

    Actual, 2016, 2, 20

    Actual, 2015, 2, 10

    Benchmark, , 2, 40

];

..and the expected output is Sum of Benchmark based on the Actual for a Specific Year. For Ex, for Year 2016 - Expected Sum of Benchmark is 65 and expected sum of Benchmark for 2015 is 40 (25 is not counted as we dont have actual for 2015). Full Expected table is as below:

 

YearActualExpected Benchmark
20145025
20151040
20166065
20177065

Sample file is attached where I have attempted the above but no luck. Any help is highly appreciated.

Thanks & Regards,

Raajesh N

1 Solution

Accepted Solutions
arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Raajesh,

Then use  below : tresesco code:

Sum(Aggr(NODISTINCT Sum( TOTAL  {<KPI={'Benchmark'}>} Value), PKey))


Thanks,

Arvind Patil

View solution in original post

8 Replies
arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Raajesh,

Please find the attachment.

Code Is as Below:

Data:

LOAD * INLINE [

    KPI, Year, PKey, Value

    Actual, 2017, 1, 30

    Actual, 2016, 1, 40

    Actual, 2014, 1, 50

    Benchmark, , 1, 25

    Actual, 2017, 2, 40

    Actual, 2016, 2, 20

    Actual, 2015, 2, 10

    Benchmark, , 2, 40

];

NoConcatenate

FinalData:

LOAD KPI,Year,PKey,Value Resident Data where WildMatch(KPI,'Actual');

Left join(FinalData)

Master:

LOAD Distinct PKey,Sum(Value) as Benchmark Resident Data where WildMatch(KPI,'Benchmark') Group by PKey ;

DROP Table Data;

Thanks,

Arvind Patil

antoniotiman
Master III
Master III

Hi,

add this to Your Script

Left Join
LOAD PKey,Value as ValueB
Resident Table
Where KPI = 'Benchmark';

See Attachment.

raajeshn
Partner - Creator
Partner - Creator
Author

Hi Arvind,

Thanks for your help and quick revert.

Unfortunately, this will not work as I cannot modify the data model (I am playing support role) and modifying the data model will be out of scope for now. Hence, trying to attempt this with the set expression.  Thanks for your help.

Thanks & Regards,

Raajesh N

raajeshn
Partner - Creator
Partner - Creator
Author

Hi Antonio,

Thanks for your help and quick revert.

Unfortunately, this will not work as I cannot modify the data model (I am playing support role) and modifying the data model will be out of scope for now. Hence, trying to attempt this with the set expression.  Thanks for your help.

Thanks & Regards,

Raajesh N

tresesco
MVP
MVP

Try exp:

Sum(Aggr(NODISTINCT Sum( TOTAL  {<KPI={'Benchmark'}>} Value), PKey))

Capture.JPG

arvind_patil
Partner - Specialist III
Partner - Specialist III

Hi Raajesh,

Then use  below : tresesco code:

Sum(Aggr(NODISTINCT Sum( TOTAL  {<KPI={'Benchmark'}>} Value), PKey))


Thanks,

Arvind Patil

raajeshn
Partner - Creator
Partner - Creator
Author

Hi Tresesco,

Thanks much for the swift response.

Expression worked almost, except that it was showing slightly different numbers. However, I took a cue from your expression and modified it as below which works like a charm. Corrected Expression is as follows,

Sum(Aggr(NODISTINCT Sum({<KPI={'Benchmark'}>} Value), PKey))

Thanks & Regards,


Raajesh N

raajeshn
Partner - Creator
Partner - Creator
Author

Hi Arvind,

Thanks, as mentioned in the above post for tresesco‌, slightly modified version works (which I also see in your attachment , though you have typed the expression from Tresesco as it is.

Thanks for your help.

Thanks & Regards,


Raajesh N