13 Replies Latest reply on Jun 21, 2018 3:40 PM by Helen Wang

# How to write calculation for 2018 and QoQ YoY

Hi All,

When I display the data numbers on the first row, I can calculate the quarter over quarter ( QoQ: I compare one quarter against its previous one, eg: (2018 Q1)/(2017 Q4)-1 );

year over year (YoY: I compare one quarter against its the same one but from the previous year. eg: (2018 Q1)/(2017 Q1)-1

It looks like this:

Before the QoQ and YoY is based on the numbers in the first line, so their calculation formulas looks like this: ( I created the quick table calculation, and then just drag it into the calculated field:

\$POS QoQ:

(ZN(SUM([\$ Net POS Actual])) - LOOKUP(ZN(SUM([\$ Net POS Actual])), -1)) / ABS(LOOKUP(ZN(SUM([\$ Net POS Actual])), -1))

\$POS YoY:

(ZN(SUM([\$ Net POS Actual])) - LOOKUP(ZN(SUM([\$ Net POS Actual])), -1)) / ABS(LOOKUP(ZN(SUM([\$ Net POS Actual])), -1))

The formula is:

IF str(DATEPART('year',[Fiscal Quarter] ))= '2018' AND str(DATEPART('quarter',[Fiscal Quarter] )) >'2'

THEN [\$POS YoY]

END

When I tried to apply this, it just won't work !

Sorry I cannot share the data. I would love any advice on the code!!!

• ###### 1. Re: How to write calculation for 2018 and QoQ YoY

HI Helen

Quit a bit difficult to guide without data, but add Attr + brackets  " attr()  "to aggregate dimension.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: How to write calculation for 2018 and QoQ YoY

Thank you so much .

May Iask why attr() may help?

• ###### 3. Re: How to write calculation for 2018 and QoQ YoY

is arregated value and need to align.

Could you please marl my answer as correct to close the thread.  Not from inbox view, but from original post.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: How to write calculation for 2018 and QoQ YoY

Could you please tell me how to make attr() work with '2018;? It is showing error.

It says cannot compare interger and string level

1 of 1 people found this helpful
• ###### 5. Re: How to write calculation for 2018 and QoQ YoY

add 'str' '   or  '2018' ==> 2018      and add attr to quarter , too.

To be honest this is very stressful to me to guide you without data.

Shin

1 of 1 people found this helpful
• ###### 6. Re: How to write calculation for 2018 and QoQ YoY

Here you go.

Attached a sample workbook too.

1 of 1 people found this helpful
• ###### 7. Re: How to write calculation for 2018 and QoQ YoY

Hari thank you so much!

I am wondering, do I have to show \$Net POS Actual as well to calculate the \$POS QoQ so that I can extract Q2 2017 QoQ?

Is there anyway I can just show POS 2017 QoQ?

• ###### 8. Re: How to write calculation for 2018 and QoQ YoY

You can try removing all the other measures and see that your calculations do not get affected. As long as they do not, you can remove it.

If, however, the calculations do get affected, you can do one of the following:

1. Hide the measure values; or

2. Move the measure value from the rows to the Detail marks.

In the below image, I have removed all the other measures, and displaying only the 2017 QoQ. I have subsequently filtered out all the null values. The updated workbook is attached.

1 of 1 people found this helpful

• ###### 10. Re: How to write calculation for 2018 and QoQ YoY

Thank you so much for your help Hari!

• ###### 11. Re: How to write calculation for 2018 and QoQ YoY

You are welcome.

1 of 1 people found this helpful
• ###### 12. Re: How to write calculation for 2018 and QoQ YoY

Hi Hari,

Hope all is well.

Thanks!

Helen

• ###### 13. Re: How to write calculation for 2018 and QoQ YoY

Hi Hari!!

If you have time, could you please help me take a look at this question as well?

Thank you so much for your help!!

I hope you have a great day!
Helen