Please don’t kill me for all the topics
I have the following table:
| week 1 | week 2 | week 3 | | type 1 | type 2 | type 1 | type 2 | type 1 | type 2 |
How do I calculate total for the week pivot? My aim is this:
| week 1 | week 2 | week 3 | | type 1 | type 2 | total | type 1 | type 2 | total | type 1 | type 2 | total |
As far as I understand
pivot_row would give me all values for each type and each week. Also I can’t use
pivot_index as I’m trying to leave it dynamic so that people can change the number of weeks they want to see.
In other words I’m trying to figure out a way to ask the following question, let’s say I am in Week 1 and Type 1: (let’s assume I’m talking about number of sessions as measure)
What is the sum of
"current" sessions + (sessions where pivot[week] = current and pivot[type] = '<this value can be hardcoded>')
The problem is also that I can’t reference the current value of pivots in functions, in order to use pivot_where.
After careful deliberation I came up with a more generic question that hopefully some people find easier to answer: How can we apply certain pivot calculations to a specific pivot level without hardcoding the values?