Chart - each date relative % to total per date

[jlampard40]

Hi guys - I have a bar chart.  Simple date series for dimension (CP_ReportDate).  I also have a dimension called CP_DaysWaiting which tells me how long a patient has been waiting for a particular procedure on any given day.

We would like to see how many patient's are being seen within different time ranges - and so we have built a slider into the report.  This works well - so we can ask the slider to show us, on each day, how many patient's we are seeing between certain times e.g. 0-7 days, 0-14 days, over 10 days etc.  This is all done via the slider.

However, I'd like to now show the PERCENTAGE of patient's, per day, on my graph, seen within a certain date range as specified by the slider.  How can I achieve this guys?  So, if user selected 0-7 days (CP_DaysWaiting) the graph shows the % of patient's per day (CP_ReportDate) achieved.

Your help is much appreciated.  Kind regards 

[vespab24]

I use a similar formula to achieve % of a customers share of total sales within a product category during a given year. I use this in a pivot table. 

example: Sum([Sell Out (Combined)])/Sum(Total <[Product Category],[Year]> [Sell Out (Combined)])

Maybe try something along the lines of below in your case or hopefully the above example, at the least, helps get you closer to achieving the desired outcome. 

=COUNT([patientID])/COUNT(Total <[CP_DaysWaiting],[CP_ReportDate]> [patientID])

[jlampard40]

Hi - thanks for your reply.  I have attached the result of using your suggestion.  As you can see in the pic, I've selected 0-7 days range and each day is split up into the respective days waiting %.  The problem is that, for example, 04/12/2019 - the % of patients seen for this date between 0-7 days should be 29%, but it's showing 13%.  When I increase the slider to the max, I'm getting %'s of over 200% for some of the dates.

Not sure what I need to do here.  If you have any advice I'd really appreciate it.  Kind regards

[vespab24]

Let's try:

=Count({<CP_DaysWaiting={"<=$(vSlider)"}>} CP_EventKey)/Count(Total <CP_DateReported> CP_EventKey)

[jlampard40]

Hi - Vespab24, this still doesn't work.  See sample data I've added below to see if you're able to get something which looks like this, using Set Analysis.  Really appreciate your help.

Rgds

[sunny_talwar · Accepted solution]

Can you try this

Count([CP_EventKey])/Count(TOTAL <CP_DateOfReport> {<[CP_DaysWaiting]>} [CP_EventKey])

[jlampard40]

Sunny you are a complete LEGEND!  Thank you!!!!!!!!

[jlampard40]

Vespab - thanks so much for your contributions.  Really appreciate your help.  Kind regards