-
1. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
You don't need 2 sheets to get what you want. Simply add the Weekly or YTD dimension to the filter and apply a condition filter to it as shown below:
-
2. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
Hi Hari, Thank you for your quick response. Currently the data which i have provided i have manually entered weekly and YTD. However in my data i have date and week number. When the parameter selection is Weekly then the chart show latest week available in data and when the selection is YTD then the chart should sum of all the weeks. Please let me know if this can be achieved without creating 2 charts. Thanks, Zak
-
3. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
I have changed the data source and posted the file with file name v1.
-
4. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
Yes, it can be achieved.
1. Create a calculated field as shown below:
2. Add the above field to the filter and set it to give the Top 1 based on Max of the above field.
3. Here is the final output:
The workbook is attached. Hope this helps.
-
5. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
Hi Hari, Thank you for your response. This is very helpful. The only challenge i see here is, i have to give filter option for Week column. Users should have options to see the data for previous week. I dont know how to achieve this using the similar approach. If this can be achieved then i can save lot of sheets which i am creating because of Week filter(separate sheet to show week and separate to YTD). I had done the same thing using Parameter, the issue with Parameter is it cant be dynamically updated as i would like to only show week with the latest data uploaded in the DB (ex. if the data loaded in DB is 25th week the filter will show 1-25 but parameter i was not able to update dynamically every week) Can you please help. Thanks, Zak
-
6. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
I am not sure I understand what your problem is exactly. And it would be good if you share your workbook and specify the problem based on it rather than with a partial dataset since your issue may not recur in it.
However, to overcome your specific parameter limitations, I would create a week number parameter list. So, when a user selects the week number, I would get the data of the selected week if it exists, and the data for the most recent week from the selected week if it doesn't exist. This is for the weekly option view. For the YTD view, data will be fetched till the selected week, or the most recent week till the selected week.
-
7. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
Hi Hari, Really sorry for not giving enough details. Please find the attached dashboard which i have created using the actual data and changed the values and column names. Let me describe what i am trying to achieve. 1. The dashboard should be capable of showing Weekly and YTD data. Currently i am creating 2 separate sheets for Weekly which i am restricting from Week column and giving filter if user want to see for previous week and YTD would be for all the week present in the data 2. When the dashboard opens the default view will have Weekly option selected and filter will be set to latest week ( ie 10, as the latest week in the data is 10) and when YTD is selected it show me sum of all the 10 weeks. 3. Using the approach i have used to develop the dashboard, show and hide sheet option is working fine with bar charts i am facing challenge when i am using pie chart. It is leaving the sheet space in the dashboard which does not have for bar chart. I tried using Parameter for week selection, but customer does not like to see Week num 1-52 in the parameter as parameter cannot be dynamically updated. Hence i had to create 2 sheets one for weekly and one for YTD. Can you please help me in achieving the above result. Thanks, Zak
-
8. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
1. Create a week parameter list including a week 0 aliased default. This should be the default value for this parameter.
2. Create a calculated field to get you the week number based on the weekly/YTD parameter and the week parameter list. (In a YTD view, the data will be fetched till the week parameter.)
3. Add the Calculated Week to the filters as shown below.
4. Add Calculated Week to the Detail mark and check out your results now.
a) Default Weekly View: Gets you the data for the most recent week for which data is available.
b) Default Yearly View: Will get you the data for all the weeks.
c) Weekly view when Week 6 is selected - Data is fetched for Week 6 only.
d) Yearly view when Week 6 is selected - Data is fetched for all the weeks up to and including Week 6.
You can now make changes as required. If you still want to work with 2 pie charts, you have already seen the challenge you are facing, so I won't be able to help you with it.
Hope this solution helps. The workbook is attached. My solution is on Sheet 5.
-
9. Re: Show & Hide Pie Chart sheet using parameter without sheet spaces
Hi Hari, Thank you very much, this is great help. I also found some other solution to fix the extra spaces for the sheet. i used blank calculated field in row and column and hides and unhides the sheets as for Bar chart. Please find the attached. i have made the changes in the "Main Dashboard" Thank you for your help. Thanks, Zak
