Calculating specific columns from a pivot

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:

@aleks 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.

Thanks for the workaround.