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

How to exclude the Orders of Particular product for a particular country in a PIVOT table

Hi,

I have the below request.

Need to calculate the Orders in Hand(OiH) excluding the brand = Nestea for Country Kenya. This change should take place only in PIVOT tabel in frontend.

Could someone help me with the expression to exclude this particular item? Please reply back at the earliest.

Thanks in Advance!!

Regards

Velan

5 Replies
Not applicable
Author

Hi Velan,

probably the simplest way to achieve this is to create a concatenated product and country field in your model and then us this in the expression

Country&'|' & Brand As Country_Brand

in your model

then

Sum({<Country_Brand-={'Kenya|Nestea'}>} [OiH])

in your expression

something like that

hope that helps

Joe

Gysbert_Wassenaar

You can probably use a set analysis expression. For example if your pivot table uses Count(OiH) as expression you can change that to Count({$-<Country={'Kenia'},Brand={'Nestea'}>}OiH). Make sure to use the correct case sensitive field names of your document, not my example.


talk is cheap, supply exceeds demand
Not applicable
Author

Actually ignore me, this is better

Not applicable
Author

Hi,

Thanks for your suggestions.

There is a small change, if I select the country in the filter as Kenya, then it should exclude the values for the brand 'Nestea' in OiH metric and but if I use the set analysis mentioned above, its disable the total values (ie) am not getting any values in the OiH metric.

Currently I have the below metric in place wherein I need to exclude the values for the brand=Nestea if I select the Country as Kenya in the filter.

=Sum({< [Year Period] = {'$(vYearPeriod)'}, [KeyFigure] = {'$(vCurHKeyFigure)'}>}[Value])

where Variable

Let vCurHKeyFigure

= '= ' & chr(39) & 'OIH (' & chr(39) & '& vCurKeyFigure & '  & chr(39) & ')' & chr(39);

where SET vCurKeyFigure     = '=Only(KeyFigureSel)'; where KeyFigureSel is Inline table

Am not clear with the vCurHkeyFigure values what it's purpose.

Not applicable
Author

Hi Gysbert,

Thanks for your suggestions.

There is a small change, if I select the country in the filter as Kenya, then it should exclude the values for the brand 'Nestea' in OiH metric and but if I use the set analysis mentioned above, its disable the total values (ie) am not getting any values in the OiH metric.

Currently I have the below metric in place wherein I need to exclude the values for the brand=Nestea if I select the Country as Kenya in the filter.

=Sum({< [Year Period] = {'$(vYearPeriod)'}, [KeyFigure] = {'$(vCurHKeyFigure)'}>}[Value])

where Variable

Let vCurHKeyFigure

= '= ' & chr(39) & 'OIH (' & chr(39) & '& vCurKeyFigure & '  & chr(39) & ')' & chr(39);

where SET vCurKeyFigure     = '=Only(KeyFigureSel)'; where KeyFigureSel is Inline table

Am not clear with the vCurHkeyFigure values what it's purpose.

Regards

Velan