• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dashboard with multiple criteria and ALL

B

brems

Member
Hello,
I'm writing a bit more of the explanation because I don't (yet) have an example to add. And my boss wouldn't be very happy when throwing the original data on the internet. ;)

I have an excel sheet with a table in and I made a dashboard where the user can define 3 criteria by means of a spinner button (forms element).

In this dashboard the user must specify ONE department with ONE year and ONE month. It's not possible to select ALL departments and ALL YEARS... The corresponding value is the looked up in the table and is shown in the dashboard. The different charts are updated accordingly.

Then I also made a new sheet where the user can define a date range and can select up to 6 criteria to choose from.
The date range is put in manually but the criteria are selected by 6 spinner buttons. In this lists I added the possibility to choose ALL values instead of ONE value.
example:
The table is about the complaints from our customers
- user enters date range
- user defines ONE Department or selects ALL Departments
- user defines ONE Contract Group or selects ALL Contract Groups
- user defines ONE Subject or selects ALL Subjects
... so up to 6 criteria
The selecting of ALL Departments or ALL Subjects is done by selecting the word "ALL".
Then by recording a macro and using the advanced filter the user can hit a button and the detailed result of the defined criteria is listed in a new worksheet. That is: all the lines from the complaint table that correspond to the defined criteria.

So far so good... :)

The thing is...
I also want to use the ALL-thing in a dashboard. In the first dashboard the user had to choose from ONE criterium and wasn't able to select ALL.
The user must be able to make different choices:
- users sets all criteria to ALL
- users specifies two criteria and sets the rest to ALL
- users specifies three criteria and sets the rest to ALL
- ...
Do you have suggestions how this can be defined? With only two variables it isn't a problem to do this but with 6 variables this get out of hand when trying defining this.


Thank you for your input.
Regards
Wim
 
Brems

This;

I don't (yet) have an example to add. And my boss wouldn't be very happy when throwing the original data on the internet. ;)
Click to expand...

Most people just moch up a sample and post that. No one wants to create a test environment for you based on your words. You already have a sample, just change the words, figures whatever.... but keep the structure the same and upload. We would be much happier to open a file and provide what insight we feel is necessary.

People who go to this small amount a effort get far more traction on forums. As you have have posted multiple times on Chandoo you should see as much.

Take care

Smallman
 
Allright... I think it's ready. :)

sheet 'db afdelingen' is the dashboard. Some results don't display correct anymore due to the changes.

sheet 'db AM' is the sheet where you can specify the 6 criteria and the date range. I want to create a similar dashboard like the sheet 'db afdelingen' but in this sheet the user can define an extra parameter: ALL (=alle)

And the source tabel is on the sheet 'basistabel klachten'

The link to the document:
https://docs.google.com/file/d/0B_-NhSp9ULywUS1zNTVKNFNYSG8/edit?usp=sharing
 
sorry, password is: vakpla

damn, and it ain't monday... o_O

I'm not at all expecting a complete solution. Just a hint to get me started again would be great. :)
 
So your data is all of this stuff?

Afdeling Contact Type ContractantGroep Omschrijving Datum Afgehandeld Datum Beginregistratie D Datum Eindregistratie D Datum Start Behandeling Dossiernummer Eindbeslissing KBC Klant Klacht Subject KlantGroep Code KlantGroep Omschrijving Markttype Oorsprong ProductNaam Product Omschrijving Quotering Voordeel Jaar Beginregistratie Maand beginregistratie Totale doorlooptijd Duurtijd behandeling Duurtijd vóór behandeling
 
Afdeling Contact Type ContractantGroep Omschrijving Datum Afgehandeld Datum Beginregistratie D
 
If you are using SUMIFS / COUNTIFS to summarize the data in the dashboard, you can use "*" whenever user selects ALL. This will work very easily.

If you want finer control, I suggest using Pivot tables to generate the totals / summaries on the dashboard and setting up slicers for various selections.
 
You must log in or register to reply here.
Back
Top