Calendar Week Ending Dimension - Pivot Table

[#1]

Hi

I have a pivot table where I am using Year, Month & WE (Week Ending) as the top dimensions.

As you  can see I have 2 cells for when the WE pass's into the following month and I require just one as it make the Gannt visualisation appear longer than it is. How can this be resolved? Thanks

[swuehl]

To which month do you want these WE to belong to?

[#3]

Hi, the latter would be my preference. Thanks

[swuehl · Accepted solution]

Maybe create an additional field in your master calendar:

LOAD Year(Date) as Year,

          Month(Date) as Month,

          Month(WeekEnd(Date)) as WeekEndMonth,

          Year(WeekEnd(Date)) as WeekEndYear, //maybe even this, too?

          WeekEnd(Date) as Weekend,

          Date,

...

Then use WeekEndMonth as dimension in your pivot table chart.

[#5]

Thanks but this did not work:

[swuehl]

Not sure what you are showing in that screenshot, it seems to work looking at a simple table like

WeekEndYear	WeekEndMonth	Weekend	Sum(Value)
2016	Jan	03.01.2016	145,29927261174
		10.01.2016	306,86295498163
		17.01.2016	404,16810107417
		24.01.2016	327,98582976684
		31.01.2016	300,87621822022
	Feb	07.02.2016	492,73973167874
		14.02.2016	219,62997671217
		21.02.2016	296,80154984817
		28.02.2016	311,3679417409
	Mrz	06.03.2016	275,56030363776
		13.03.2016	431,4491267316
		20.03.2016	274,97564293444
		27.03.2016	359,32481959462
	Apr	03.04.2016	290,72316591628
		10.04.2016	338,93176536076
		17.04.2016	396,28496621735
		24.04.2016	194,58284685388
	Mai		1479,4561761897
	Jun		1334,1142497025
	Jul		1796,8986579683
	Aug		1599,9991677236
	Sep		1263,3464721031
	Okt		1658,1374940928
	Nov		986,26576885581
	Dez		1071,1453956552
2017	Jan	01.01.2017	325,30479370616

[#7]

Sorry you are correct - I forgot to change the Weekend field to the new calendar field. It works perfectly so thank you