How do I calculate the average for each day of the week?
I need to be able to calculate the average visits per location for each day of the week. Any help is greatly appreciated! I haven't been able to figure it out. See example below. The yellow highlighted is what I need. I tried the formula avg(@"[SATURDAY]",0,M1) but that didn't work.
0
Comments
Hi Crystal,
You can do this by using the colname() function. See this article for rowname() and colname() calculations: https://community.targit.com/hc/en-us/articles/17792755367068-Rowname-and-Colname-calculations
In my example, I have included only Fridays, Saturdays and Sundays, but from the description you should be able to expand to all seven weekdays.
I am assuming that your crosstab contains only one measure, m1.
You will need to insert seven calculations, inserted as a new measure, one for each weekday.
Example, Monday: if colname("Monday") then sum(0, 0, m1) else 0. Likewise for the other six weekdays.
If the Monday calculation is the first calculation you add, this calculation can be referred to as m2. Likewise for the other six weekdays.
Your average calculation (for Monday), inserted as a new column, will then be: avg(all, 0, m2). Likewise for the other six weekdays.
Eventually, you can go to the Properties Smartpad of this crosstab, and in Visibility hide all the intermediate calculations.
BR / Ole
Thank you! I'm unfortunately getting the following error. Am I doing something wrong or does this have to do with my version of Targit?
Hi Crystal,
In the linked article, at the top of the article, you can see that the rowname/colname functions were released in January and that you need to be on the 2025 January release, build 25.02.07520 or later for this to work.
Unless you are on a cloud installation of TARGIT - because then you will always be on the latest release with access to the newest features.
BR / Ole
Please sign in to leave a comment.