Exclude certain dimension values

[agbcn]

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

[Kushal_Chawda]

@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). 

[agbcn]

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

[Kushal_Chawda]

@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.

[agbcn]

@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", ...

[Kushal_Chawda]

@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)

[agbcn]

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

[Kushal_Chawda · Accepted solution]

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)

[agbcn]

@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