Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
Actually ignore me, this is better
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.
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