Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Not applicable

Apply dimension solely to one of many expressions.

Hello, everyone. Hope you are doing good. I want to achieve the pivot table below.

MODEL.png

FABRIC is a dimension, SUM(PRODUCTION) is the total production for that fabric, Right beside it, the total amount for each account.

It would be so lovely to be able to just drag the ACCOUNT dimension over SUM(AMOUNT) expression and achieve this, but, as you may have already guessed, I cannot. If I put the dimension in the upper section of my pivot table, it applies to EVERY expression (to SUM(PROD.) too!) and I don't want that: I want ACCOUNT dimension to apply solely to SUM(AMOUNT).

Can this be achieved?

Thank you very much!

Martín.

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Apply dimension solely to one of many expressions.

Hi Martin

How about creating Total Prod as calculated dimension with following definition:

=AGGR(SUM(PRODUCTION) , FABRICS)


Lukasz

View solution in original post

8 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Apply dimension solely to one of many expressions.

Try enabling Show Partial Sums for the FABRICS and ACCOUNT dimensions and choose to show Subtotals on Top. You can find both options on the Presentation tab of the properties window of the pivot table.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Apply dimension solely to one of many expressions.

Hi Martin

How about creating Total Prod as calculated dimension with following definition:

=AGGR(SUM(PRODUCTION) , FABRICS)


Lukasz

View solution in original post

Highlighted

Re: Re: Apply dimension solely to one of many expressions.

Gysbert is absolutely correct, see attached qvw.

Highlighted
Not applicable

Re: Apply dimension solely to one of many expressions.

It did not work,  Gysbert, but now that you mention it, I think I didn't make myself clear. I have corrected the picture. I don't want the total amount for each account; I want to be able to get SUM(AMOUNT) for each of the accounts, but if I try so, I end up dividing the other expression too (SUM(PRODUCTION)).

Thanks for your answer.

Highlighted
Not applicable

Re: Apply dimension solely to one of many expressions.

Thanks, Bill, and thank you very much for your answer and example. However, the difference between your model and mine, is that, in yours, TOTAL PRODUCTION is the sum of every AMOUNT in the row, but in mine, AMOUNT and PRODUCTION have no relation whatsoever: TOTAL PRODUCTION is not the sum of the amounts.

Thanks again, anyway!

Highlighted
MVP & Luminary
MVP & Luminary

Re: Apply dimension solely to one of many expressions.

Use sum(AMOUNT) as expression. Disable Show Partial Sums for all dimensions and add the calculated dimension lmastalerz described. And if you need more help post the document you're working on or a representative sample document.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Apply dimension solely to one of many expressions.

This is precisely what I was looking for! I did not know about AGGR usage. Thank you very very much, Lukasz!

Highlighted
Not applicable

Re: Apply dimension solely to one of many expressions.

Totally with you, Gysbert, He was right, indeed. I really appreciate your guidance (it is not the first problem you help me with, and surely I hope it won't be the last!)

Thanks!