4 Replies Latest reply on Feb 26, 2017 10:54 PM by Zorikto Dabaev

How to create calculation of YoY change in % for multiple measures in 1 table

Hello, community!

I need help with calculation of YoY change in % for multiple measures in 1 table

Please check screen shot - I showed what I need exactly

Checked different sources and I was able to do only for 1 measure, not multiple.

Maybe somebody already done the same? Looking for any tip.

• 1. Re: How to create calculation of YoY change in % for multiple measures in 1 table

Hi Zorikto

The standard(?) or easier way is to create two worksheets and merge on Dashboard.

According to your comment you want to create the table on single worksheet, so here is that option.

However, this approach is quite a bit troublesome...

Assuming you need 4 measures, you need to create 4x3 = 12 fields.

And to make the view better, you need to duplicate the data source and create total three.

From edit data source, create union and add three tables.

Create 4x3=12 calculated fields.

[Order Qty 1]

if attr([Table Name])="Orders" then sum([Order Quantity]) end

[Profit 1]

if attr([Table Name])="Orders" then sum([Profit]) end

[Sales 1]

if attr([Table Name])="Orders" then sum([Sales]) end

[Shipping Cost1]

if attr([Table Name])="Orders" then sum([Shipping Cost]) end

[Order Qty YOY]

if attr([Table Name])="Orders1" then

(zn(sum([Order Quantity]))-zn(lookup(sum([Order Quantity]),-1)))/

abs(zn(lookup(sum([Order Quantity]),-1))) end

[Profit YOY]

if attr([Table Name])="Orders1" then

(zn(sum([Profit]))-zn(lookup(sum([Profit]),-1)))/

abs(zn(lookup(sum([Profit]),-1))) end

[Sales YOY]

if attr([Table Name])="Orders1" then

(zn(sum([Sales]))-zn(lookup(sum([Sales]),-1)))/

abs(zn(lookup(sum([Sales]),-1))) end

[Shipping Cost YOY]

if attr([Table Name])="Orders1" then

(zn(sum([Shipping Cost]))-zn(lookup(sum([Shipping Cost]),-1)))/

abs(zn(lookup(sum([Shipping Cost]),-1))) end

[Order QTY Title]

if [Table Name]="Orders2" then "Order Quantity" end

[Profit Title]

if [Table Name]="Orders2" then "Profit" end

[Sales Title]

if [Table Name]="Orders2" then "Sales" end

{Shipping Cost Title]

if [Table Name]="Orders2" then "Shipping Cost" end

Create header for filter (hide last year only from title and YOY)

[Table Name]+"_"+str(Year([Order Date]))

if ([Table Name])="Orders2" then "Measure"

elseif

([Table Name])="Orders" then str(year([Order Date]))

elseif

([Table Name])="Orders1" then "YOY"

end

Put 4x Min of number of record (this is place holder with number "1")

Put each quantity related three fields to top mark.

Hide Header of last year from title and YOY

Thanks,

Shin

1 of 1 people found this helpful
• 2. Re: How to create calculation of YoY change in % for multiple measures in 1 table

Yes, I see that is troublesome as you said.

I found one more way combining field calc and pivoting measure fields in initial data source

I attached my example, however it has big restrictions - as measure format have to be the same to use correctly aggregating functions

I was wondering maybe there is possible way using LOD's?

And as you said it seems that combining on dashboard will be easiest.

Thank you!

Zorikto

• 3. Re: How to create calculation of YoY change in % for multiple measures in 1 table

Hi Zorikto

I'm not sure LO helps in this case. if you pivot the data, it may help.

You still need last year, this year, and YOY as new fields.

And you will lose the header of "year" and it becomes this year, last year.

Thanks,

Shin