topic Re: Expression for daily percentages in QlikView

Expression for daily percentages

peterderrington — Thu, 27 May 2021 09:22:18 GMT

Hi,

I have a chart that shows a percentage over a date dimension. The expression I'm using is this - num(Sum([OWN CLOTHING])/sum([No. PATIENT ON WARD]),'#,##0%')

The end result looks like this:

Which is great but now i'm trying to create a bar chart version which uses the Day dimension and am having problems creating the expression to show the average on a daily perspective.

I've tried this expression - Median(Total Aggr(num(Sum([HOSPITAL WEAR])/sum([No. PATIENT ON WARD]),'#,##0%'),Day)) but it shows the same figure for every day and the result looks like this:

Any ideas?

Thanks

Re: Expression for daily percentages

sergio0592 — Thu, 27 May 2021 12:28:53 GMT

Hello,

With the field WeekDay in your load statement, try with:

avg(TOTAL <WeekDay> Sum([OWN CLOTHING])/sum([No. PATIENT ON WARD]))

Re: Expression for daily percentages

peterderrington — Thu, 27 May 2021 13:06:23 GMT

Thanks for the suggestion Sergio, i can add "Load Weekday" into the Load statement but where should the "From" direct to?, Without it, it doesn't the field and your suggested solution doesn't work.

Re: Expression for daily percentages

sergio0592 — Fri, 28 May 2021 12:15:18 GMT

Hi, what do you mean with the From?

Re: Expression for daily percentages

peterderrington — Fri, 28 May 2021 12:36:58 GMT

I mean in a load statement it normally looks like this:

LOAD Date,
Day,
[No. PATIENT ON WARD],
.....loads more fields here......
[Total (Activity Champ)],
[Total (Other Staff)]
FROM
[C:\Users\hrpde\Desktop\Active Ward Data.xlsx]
(ooxml, embedded labels);

It needs to know where its loading things from. I've tried without a FROM and the field and expression you suggested doesnt work, leading me to think i'm missing something

Re: Expression for daily percentages

sergio0592 — Fri, 28 May 2021 12:43:33 GMT

Ok, your mean in the preceding load. So, in your exemple, try with :

LOAD Date, Day, WeekDay(Day) as WeekDay, [No. PATIENT ON WARD], .....loads more fields here...... [Total (Activity Champ)], [Total (Other Staff)] FROM [C:\Users\hrpde\Desktop\Active Ward Data.xlsx] (ooxml, embedded labels);

Re: Expression for daily percentages

peterderrington — Fri, 28 May 2021 12:50:29 GMT

Nope it doesn't like that

Re: Expression for daily percentages

sergio0592 — Fri, 28 May 2021 13:20:06 GMT

Strange, for me it works. Can you share your qvw?