Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
peterderrington
Creator II
Creator II

Expression for daily percentages

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:

line chart - date.png

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:

Bar chart - day.png

Any ideas?

Thanks

7 Replies
sergio0592
Specialist III
Specialist III

Hello,

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

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

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.

sergio0592
Specialist III
Specialist III

Hi, what do you mean with the From?

peterderrington
Creator II
Creator II
Author

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

sergio0592
Specialist III
Specialist III

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

 

peterderrington
Creator II
Creator II
Author

Nope it doesn't like that

error expression.png

sergio0592
Specialist III
Specialist III

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