 # Row/Column totals

(Bhanu) #1

Hi Team is there a way i can achieve this using a measure or table calculations? if yes how?

“Yr ID” and “Yr Qtr Id” are the dimensions which used as pviot and “Resp Que Id” is a normal dimension which used directly on the report and Tasks is a measure

i need totals separated for every year

0 Likes

(Izzy) #2

This is actually pretty hard to do dynamically, I think.

If you have multiple pivots, but want to create a total for each outer pivot, you have to do some complex table calculations.

You could do it more statically with pivot_offset(), if you know how many pivot columns you’re going to have, but that will all break if you change the sorting or add new columns.

I would be super impressed if someone could come up with a dynamic way to do this with a table calculation!

1 Like

(Dawid) #3

I think the main showstopper here is that with pivot functions we have no way of referring to a certain pivot… I believe if we could it would open a lot of more possibilities

1 Like

(Daan Visker) #4

Hi Bhanu,

After reading your question, I have been trying to come up with some solution for this.
However it is quite case-specific, it should be able to be adjusted quite easily if something small changes and is written to be sort-of generic.

A few notes:

1. It is assumed that there is a filter on ‘The Past 3 Years’. So this year and two years back.

2. Each year will have an own Table Calculation.

3. My code uses the ‘Quarter Of Year’ on the time dimension_group, but should easily be adjusted to the Qtr Id in your example, using substring(yr_qtr_id,6,1).

4. The Table Calculations will NOT be shown within the table, but put to the outer right with all Table Calculations based on measures.

``````         coalesce(
pivot_where(\${year} = trunc_years(add_years(0,now()))
AND \${quarter_of_year} = "Q1"
, \${your_measure}
)
,0
)
+
coalesce(
pivot_where(\${year} = trunc_years(add_years(0,now()))
AND \${quarter_of_year} = "Q2"
, \${your_measure}
)
,0
)
+
coalesce(
pivot_where(\${year} = trunc_years(add_years(0,now()))
AND \${quarter_of_year} = "Q3"
, \${your_measure}
)
,0
)
+
coalesce(
pivot_where(\${year} = trunc_years(add_years(0,now()))
AND \${quarter_of_year} = "Q4"
, \${your_measure}
)
,0
)
``````
• The code above is for the Table Calculation on the current year. You can then make another for one year further back by changing add_years(0,now()) to add_years(-1,now()), etc.

• To fix number 4 of my notes above, you could hide the measure and add Table calculations for each year-quarter first, using separate ‘pivot_where()’ in the code above. That would be quite tedious though.

I hope this helps a bit!

2 Likes

(Bhanu) #5

Thank you very Much Daan, will try and let you know the result

0 Likes

(Izzy) #6

Daan the genius! Nice But, the part that gets me is:

That was the piece that I couldn’t figure out how to possibly work around and ended up giving up on.

0 Likes