Calculating specific columns from a pivot

  • 2 May 2017
  • 6 replies

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

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:


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,