Calculating previous year weekday in a Pivot table

[dafnis14]

Hi,

I have a request to display a pivot table with the following dimensions & Measurements:

My question is how should I calculate the same weekday last year?

Thanks!

Message was edited by: Dafnis X Attached sample data

[sunny_talwar]

May be you can try with this

The As-Of Table

[dafnis14]

Hi Sunny,

I don't think I follow..

I guess I need to create some sort of a key with the following fields:

• Week Day number (from 1 to 7)
• Week number in month (1-6)
• Month number

and then be able to get the key for the current year and the previous year.

Does it make sense?

Thanks!

[sunny_talwar]

Would be WeekDay and Week Month or WeekDay and WeekYear?? Best way to figure it out would be to share a sample and let us play around with it. Also, please provide the expected output from the sample provided

[dafnis14]

It would be :

weekday+ number of eek In month+month+year

so for 01/07/2016 (Friday, the first week of July)

it would be:

6+1+7+2016

I'll try to prepare some data. The desired output is :

Thanks for your kind help!

[dafnis14]

Hi Sunny,

I have attached sample data to the original post.

Thanks!

[sunny_talwar]

What if a month has 5 Fridays and next year it only has 4 Fridays? I thought Week and Year might be enough so that we don't have to worry about changing month? What do you think? Do you need it to be in a month also?

[sunny_talwar · Accepted solution]

The linkage is not quite what you wanted and this is still not perfect, but you should get the idea of what I trying to do....

[dafnis14]

Thanks, Sunny!