topic Re: Chart - each date relative % to total per date in QlikView

Chart - each date relative % to total per date

jlampard40 — Sat, 16 Nov 2024 19:29:42 GMT

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

Re: Chart - each date relative % to total per date

vespab24 — Mon, 09 Dec 2019 21:09:26 GMT

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])

Re: Chart - each date relative % to total per date

jlampard40 — Tue, 10 Dec 2019 08:11:44 GMT

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

Re: Chart - each date relative % to total per date

vespab24 — Tue, 10 Dec 2019 15:13:22 GMT

Let's try:

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

Re: Chart - each date relative % to total per date

jlampard40 — Tue, 10 Dec 2019 15:24:00 GMT

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

Re: Chart - each date relative % to total per date

sunny_talwar — Tue, 10 Dec 2019 15:37:18 GMT

Can you try this

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

Re: Chart - each date relative % to total per date

jlampard40 — Tue, 10 Dec 2019 15:39:42 GMT

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

Re: Chart - each date relative % to total per date

jlampard40 — Tue, 10 Dec 2019 15:51:23 GMT

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