Hey there, I am trying to figure out how to make calculations on specific columns from a pivot. In this example, I want to look at Monday sales, Tuesday-Friday sales, and Saturday-Sunday sales. I ran the query with sales by individual days, but I cannot figure out how to do sums of the specific days.
Hey @nomadfromcincy
To achieve this you can use the pivot_index() table calc function. In my example below I have count of orders pivoted by day of the week:
I’ve used the pivot_index
function to add up the desired pivoted columns:
Monday:
pivot_index(${order_items.count}, 1)
Tuesday - Friday:
pivot_index(${order_items.count}, 1)+ pivot_index(${order_items.count}, 2)+ pivot_index(${order_items.count}, 3)+ pivot_index(${order_items.count}, 4)+ pivot_index(${order_items.count}, 5)
Saturday - Sunday:
pivot_index(${order_items.count}, 6)+ pivot_index(${order_items.count}, 7)
You could hide the measures from visualisation if you’d like to show the grouped columns only:
Thank you, that worked.
@aleks1 Is it possible to dynamically select columns based on some condition? For example, @nomadfromcincy’s example, the pivoted columns 0 - 6 have count data for the two genders, f and m. Suppose we don’t have data for males for the week index 4, and one apples a filter on gender (gender = m), the pivot_index() approach provided breaks down as the positional indices won’t apply. One would need to manually update the expression in the table calculation, which is rather inconvenient.
Aaah…there’s a pivot_where()
I can exploit 🙂 Should do for my use case.
Thanks for the workaround.
i appreciate the share
thank you,