Doing a different operation on each day of the selection
Hello guys,
We have a table which contains informations on consulations (each row represents a different consultation). And we would like to calculate the average occupancy.
The problem is that the number of slots for a day changes from one week day to another. So in order to have the correct number we must first calculate the occupancy of each day and then we can have the general occupancy using an average function.
Based on what I saw it is possible to retrieve the number of slot for the day of the week by using a function inside a variable.
But I don't know how to do the calculation on each date.
So basically we would need something like (sorry for the syntax I'm new to clik) :
AVG({for each date of the selection} COUNT(date) / $(slots(WEEKDAY(date))))
Does Clik allows to do this type of calculation ? And if so, could you please help me to do it ?
It would be better if you could provide an example on your data.
The best way would be in the load script associate a table with the Slot/Week to a Calendar linked to the fact table by the Date Field as I show you in the image.
