Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a table with a column named "basket" containing many repeated values of 1,2,3,4,5. (Egg baskets with a different number)
Each row represents one egg.
I want to have a dashboard ONLY concerning the dimension which values are 1,2 and 3. (I want to omitted 4 and 5 completely)
For example, i want to count the total number of eggs in baskets 1,2 and 3. I want to present this data as a KPI.
Measure: count({$<basket={'1'}>} basket) + count({$<basket={'2'}>} basket) + count({$<basket={'3'}>} basket)
Next, i want to create a pie chart to show the proportions of eggs among baskets 1,2 and 3.
Dimension: basket
Measure: count({$<basket={'1'}>} basket) + count({$<basket={'2'}>} basket) + count({$<basket={'3'}>} basket)
When the user clicks on a pie (say basket 1), i want the KPI created originally to change its value to counting the eggs in basket 1 rather than all three baskets. How do i go about this without loading a new table which omits basket 4 and 5. Am i going about this wrongly.
Regards,
Gaston
Hi Gaston,
first of all change your pie chart expression from:
count({$<basket={'1'}>} basket) + count({$<basket={'2'}>} basket) + count({$<basket={'3'}>} basket)
to:
count({$<basket={'1','2','3'}>}basket) //it's more elegant
and then use if() with getfieldselections() formula to achieve your goal, so this is the formula for the kpi:
=if(isnull(GetFieldSelections(basket)),
'# of 1,2,3 basket' & chr(13)& chr(13)&count({<basket={'1','2','3'}>}basket),
'# of '&GetFieldSelections(basket)& 'basket' & chr(13)& chr(13)&count(basket))
Hi Gaston,
first of all change your pie chart expression from:
count({$<basket={'1'}>} basket) + count({$<basket={'2'}>} basket) + count({$<basket={'3'}>} basket)
to:
count({$<basket={'1','2','3'}>}basket) //it's more elegant
and then use if() with getfieldselections() formula to achieve your goal, so this is the formula for the kpi:
=if(isnull(GetFieldSelections(basket)),
'# of 1,2,3 basket' & chr(13)& chr(13)&count({<basket={'1','2','3'}>}basket),
'# of '&GetFieldSelections(basket)& 'basket' & chr(13)& chr(13)&count(basket))
Hi Michele,
Thank you it worked! May i bother you by asking you the functions of '# and chr(13)
Regards,
Gaston
chr(13) is the ascii code for carriage return.
# is the abbreviation of "number"