Question

Calculating specific columns from a pivot

  • 2 May 2017
  • 6 replies
  • 6220 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

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.

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

Thanks for the workaround.


i appreciate the share


thank you,

Reply