Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

naykesh4ev
Contributor

Set Analysis showing all values but Dimension to only show selection

Hi

I have an expression in a pivot table showing all values, no matter the "brand" selection.  However if I do make a selection, I want only the field selected to show, however still keeping the total value. 

Attached herewith is a sample of a Sales QV Model and related data in excel format.

In this model, I have sales of brands through 3 different channels.  Each branch is part of a category.

So in my model, when i select "apple" , the pivot table should only show "apple" and not all other brands. 

Please can you assist.

Please note the total sales should be 33923 and if i select fruit under category and it changes to 14190, that is still fine.

Kind regards

Nayan

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis showing all values but Dimension to only show selection

May be this:

Sum({State1*$<Brand=>} TOTAL<Channel,Category> Aggr(DISTINCT Sum({State1*$}{$<Brand=>}(Sales)),Channel,Category)) * Avg({State1*$}TOTAL <Brand>1)

7 Replies
Not applicable

Re: Set Analysis showing all values but Dimension to only show selection

Hi Plz check the attachment once,

In expression i have changed it , now its working based on selection.

Plz check once if it is giving results as you expected its k, if it is not i am too wait to get the solution.

naykesh4ev
Contributor

Re: Set Analysis showing all values but Dimension to only show selection

Thank you for the reply Umamaheswar

The solution is almost there.  If apple is selected, it must still show total of 33,923.  Then when I select "Fruit" under the category, then it must give a total of 14,190.

Can this be done.

Kind regards

Nayan

MVP
MVP

Re: Set Analysis showing all values but Dimension to only show selection

Is this the goal?

Capture.PNG

Expression:

Sum({$<Brand=>} TOTAL<Channel,Category> Aggr(DISTINCT Sum({$<Brand=>}(Sales)),Channel,Category)) * Avg(TOTAL <Brand>1)

naykesh4ev
Contributor

Re: Set Analysis showing all values but Dimension to only show selection

Hi Sunny T

Yes, that is correct.  Thank you.

Kind regards

Nayan

naykesh4ev
Contributor

Re: Set Analysis showing all values but Dimension to only show selection

Hi Sunny T

If i had to apply alternate states to the expression, where would i put {State1*$} in the expression.  I've tried, however, I'm unsuccessful.

Please can you assist.

Kind regards

Nayan

MVP
MVP

Re: Set Analysis showing all values but Dimension to only show selection

May be this:

Sum({State1*$<Brand=>} TOTAL<Channel,Category> Aggr(DISTINCT Sum({State1*$}{$<Brand=>}(Sales)),Channel,Category)) * Avg({State1*$}TOTAL <Brand>1)

naykesh4ev
Contributor

Re: Set Analysis showing all values but Dimension to only show selection

Thank you again.  Much appreciated .

Community Browser