your cart

How to create instructive rank button

    In last post, I showed how to create rank button with parameter action    
    But it has some weak points:
    1. If it's published on a server and need refresh extract, it will fail because button comes from an external excel file. To solve this, you need create same data on your datasource, but not every analyste has "Write" access on database.
    2. No matter how many cities in a state, all button will be still shown.
    In this post, I will show you how to fix these 2 points.
    All the process is same as last post. But now, we need create some fake button to replace external excel file.
    Button: when city number is more than 50, it still show 6 button becasue the sales after 50 rank is too small to show.
CASE MONTH([Order Date])
WHEN 1 THEN '1-10'
WHEN 2 THEN '11-20'
WHEN 3 THEN '21-30'
WHEN 4 THEN '31-40'
WHEN 5 THEN '41-50'
ELSE '51-60'
END
    Then redo the button sheet with same process but use the new calculated field "Button" just created above.
    Then, create state set.

    Create another 3 calculated fields.
    City Numbers for Button: this is different from "City Numbers" which will be affected by filter. City Numbers for Button will get city number by set action without filter so that button will not be affected by filter. If use parameter in "Button" sheet as last post, you will get inconsistent city number on each button causing mistakes. If you use filter, you will filter out some button because not every state has sales in each month.
{MAX(IF [State Set] THEN {FIXED [State]:COUNTD([City])} END)}
    Max Index:
SUM(
IF [City Numbers for Button]<=10 THEN 1
ELSEIF [City Numbers for Button]>10 and [City Numbers for Button]<=20 THEN 2
ELSEIF [City Numbers for Button]>20 and [City Numbers for Button]<=30 THEN 3
ELSEIF [City Numbers for Button]>30 and [City Numbers for Button]<=40 THEN 4
ELSEIF [City Numbers for Button]>40 and [City Numbers for Button]<=50 THEN 5
ELSE 6
END
)
Showed Button:
IF [Index]<=[Max Index] THEN 'Show'
ELSE 'Not Show'
END
    Now drag "Showed Button" to color and compute using "Button" .

    Now go to dashboard, delete parameter action of choosing state and change it to set action.

    Create a new set action to replace deleted parameter action. Make sure you choose "Run on single select only" and "Remove all values from set".

    Now click on any state which has less than 50 citis sales.

    Then go to "button" sheet and edit color as below: Show is bule, Not show is white.

    After that, you will notice only first 2 buttons left, others "disappear".

    Go back to dashboard, and click different state, you will see button number will change based on city number which has sales.  When you unselct a  state, all button will be shown. This function will let end user not touch the buttons are unuseful.


Blog Category