Question

# Calculating specific columns from a pivot

• 6 replies
• 15325 views

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.

### 6 replies

Userlevel 2

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.

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

Aaah…there’s a `pivot_where()` I can exploit 🙂 Should do for my use case.

Userlevel 1

Thanks for the workaround.

i appreciate the share

thank you,