topic Re: Exclude certain dimension values in App Development

Exclude certain dimension values

agbcn — Sat, 16 Nov 2024 18:01:09 GMT

Hi,

Let's assume I have a table with the following fields

Id Category Family Cont

1 A A1 1

2 A A2 1

3 A A3 1

4 B B1 1

5 B B2 1

6 C C1 1

7 C C2 1

and I also have 3 KPI charts for every Category. When I click a KPI chart, a variable (vKPI) is set to the name of the KPI chart. So if I click KPI chart corresponding to Category A, my variable is set to A and so on.

I'd need a bar chart to show the Sum(Cont) by Family. The requirement is I must show only the Family values corresponding to the Family stored in variable vKPI. So if VKPI is set to A, The Families shown in the bar chart must be A1, A2 and A3. If is set to B, Families shown are B1 and B2. Every time I click on KPI chart, the bar chart must change to show the corresponding values (I already know how to do that)

I tried to do it by using Match() or If(), but it doesn't seem to work.

Thanks in advance for your help

Regards,

Alex

Re: Exclude certain dimension values

Kushal_Chawda — Sat, 03 Oct 2020 21:04:15 GMT

@agbcn If you already have setup variable with category values then you can try creating barchart with Dimension Family and expression as below

sum({<Category ={"$(vCategory)'}>}Cont)

Where vCategory is variable setup by KPI (Assuming).

Re: Exclude certain dimension values

agbcn — Sat, 03 Oct 2020 21:30:47 GMT

Hi,

Thanks for your reply.

Your solution is very similar to what I'm already doing. My measure expression is:

if(vKPI = 'ACL', Sum({<Category= {'3'},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont),
if(vKPI = 'ACE',Sum({<Category= {'2'},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont),
if(vKPI = 'AIN',Sum({<Category= {'1'},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont),
if(vKPI = 'AOT',Sum({<Category= {'5'},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont),
if(vKPI = 'ARE',Sum({<Category= {*},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont),
if(vKPI = 'ALE',Sum({<Category= {'6'},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont),
if(vKPI = 'IIN',Sum({<Category= {'9'},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont),
if(vKPI = 'APR',Sum({<Category= {'4'},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont)
,
0))))))))

And my Dimension expression is = Family

However, when I click on a KPI chart, the bar chart change the values correctly but shows all families and I need to show only those families which belong to the selected category.

Regards

Àlex

Re: Exclude certain dimension values

Kushal_Chawda — Sat, 03 Oct 2020 21:39:34 GMT

@agbcn you said on clicking KPI variable sets the category. What value it captures in variable? Because in your set condition value of vKPI is different then Category values used in set analysis.

Re: Exclude certain dimension values

agbcn — Sat, 03 Oct 2020 21:46:59 GMT

@Kushal_Chawda When I click the KPI chart, the variable sets the category name and the number in the set condition is the Category Id (numerical). The vKPI value is used in some other places in my app and it's easier the name than the value.

So vKPI stores "ACL", "ACE", ...

Re: Exclude certain dimension values

Kushal_Chawda — Sat, 03 Oct 2020 21:47:23 GMT

@agbcn Why don't you just use Category name in set like below? Doing so you don't need to create so many expressions. Clicking on KPI will store the value and that value will get passed to set analysis which will work dynamically

Sum({<Category_Name= {'$(vKPI)'},[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont)

Re: Exclude certain dimension values

agbcn — Sat, 03 Oct 2020 22:06:50 GMT

I already did it, but it didn't work ...

I mean, the sum of Cont is ok, however I still see Families which doesn't belong to the category

Re: Exclude certain dimension values

Kushal_Chawda — Sat, 03 Oct 2020 23:04:53 GMT

This is my last try.. I mean share your sample app otherwise guessing will go on

try calculated dimension

=aggr(only({<Category_Name= {'$(vKPI)'}>}Family),Family) // check Suppress null value option

then your expression will go like blow

Sum({<[OrdDt.Fisc.FiscalYear]={$(vCurrentYearl)}>} Cont)

Re: Exclude certain dimension values

agbcn — Sat, 03 Oct 2020 23:46:00 GMT

@Kushal_Chawda It seems to work! I'm still wondering why the other solutions didn't!

Thanks for your time and help. I really appreciate it

Regards,

Alex