Quarter and YTD totals

[#1]

Hi all,

I am trying to build a chart that would look like:

My current Dimensions are:

Product_Name

Calender_Quarter

My current Expression is:

Sum({$}Product_Counter)

Using the above I am able to get the Q1, Q2, Q3 totals, but I am drawing blanks as to how I can add in the YTD totals in there also.

Any help in the right direction would be appreciated.

[Gysbert_Wassenaar]

Create a new field Period in the script

YTD:

LOAD * inline [

Period, Calender_Quarter

Q1, Q1

Q2, Q2

Q3, Q3

YTD, Q1

YTD, Q2

YTD, Q3

];

Then use Period as dimension instead of Calender_Quarter.

[#3]

Thanks for the reply above. Although this is a solution, I am hoping that its not the only one. I already have a calendar table and the Orders table has the product and orderdate.

[Gysbert_Wassenaar]

An alternative is to use four expressions in a straight table instead of using the Calender_Quarter dimension in a pivot table:

Q1: Sum({$<Calender_Quarter={'Q1'}>}Product_Counter)

Q2: Sum({$<Calender_Quarter={'Q2'}>}Product_Counter)

Q3: Sum({$<Calender_Quarter={'Q3'}>}Product_Counter)

YTD: Sum($<Calender_Quarter>}Product_Counter)

If you like I can come up with even more complicated solutions like a calculated dimension using the valuelist function.

[#5]

I like this option! I keep forgetting the straight table! I always default to the pivot. But in this situation because I am not allowing the user to do any type of drill down, and straight table will work.

[#6]

Never happy with first design, I wanted to step it up a bit more.  So now I am after this look:

I had to step away from the straight table and use a pivot.

I wonder if I could put the YTD in Aggr?

[#7]

Gysbert:

Adding Period to the script did get me almost there. So thank you very much for your input.

[#8]

Adding in the Period as suggested by Gysbert I get the following look.

If I could now narrow down the YTD to just show the Net Sales and not the Goals and Actuals.