Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concatenated value in set analysis

Can someone tell me why this doesn't work:

Sum({<DiscountPercentKey={"$(=[Customer Number]&'_'&[Item Category Code 2])"}>}[Discount Percent])

Here are the values that I'm looking to return:

Capture.PNG

When I put the concatenation directly in the expression like this: [Customer Number]&'_'&[Item Category Code 2]  ; I get this:

Capture2.PNG

If I put the hard coded value in like this: Sum({<DiscountPercentKey={'14546_FUN'}>}[Discount Percent])  ; I get my percentage returned. I've checked that it's trimmed. What am I doing wrong?

3 Replies
Highlighted
Not applicable

Re: Concatenated value in set analysis

I've removed the underscore used in the concatenation from the load script to eliminate that. It didn't help. I then added the additional "=" before the $. Now, I have the following expression that returns the TOTAL sum for all discounts listed for a single customer:

Sum({<DiscountPercentKey={"=$(=[Customer Number] & [Item Category Code 2])"}>}[Discount Percent])

Why am I getting the total sum for all percentages?

Highlighted
Specialist
Specialist

Re: Concatenated value in set analysis

As I see in this case you need to agregate your values in order to groups them, the set analysis maybe is not your best solutions.

Try...

Sum(Aggr(Sum([Discount Percent]),DiscountPencentKey))...

See what happen

Highlighted
Not applicable

Re: Concatenated value in set analysis

Thank you for the reply Jolivares! Unfortunately that doesn't work. I've since updated my table though. I now have a pivot chart with the following:

Item Category Code 2, Item Number, List Price

CAT, Item1, $25

DIL, Item2, $30

EPI, Item3, $40

etc.......

I want to add discount percent as an expression (or dimension) to calculate final price off of List Price. My discounts are based on Customer > Discount Category > Discount Percent. So each customer gets an individual discount per item category. That table (Discounts) has an association to my customers table. So the Items table and Discounts tables aren't linked. BUT, the Item Category Code 2 in the Items table has the same values as Discount Category in the Disounts table. Because I limit the pivot chart to one selected customer, the categories in the Discounts table gets limited to only the categorys > discounts for the selected customer:

Discount Category, Discount Percent

CAT, 0.7

DIL, 0.65

EPI, 0.7

So, now I'm trying to do something like Sum({$<[Discount Category]={$(=[Item Category Code 2])}>}[Discount Percent])  which I know from other posts isn't possible. I just don't know another way to do it.

Any further help would be greatly appreciated.