First Day of Week in Pivot Table.

[infosense_devel]

Hello,

          i have have created pivot table table as below.

Week Day	NAME	VAUS_OU	VCHN_OU	VDX_OU	VEIT_OU	VESA_UO	VFSA_OU	VGMBH_OU	VHK_OU	VLTD_OU
6-Jun-16		936973	7447992	2260957	13403574	18872963	5841179	16016083	5967167	10382340

     note:- i have selected "6-Jun-16" to explain issue.

i have two dimensions "Week Day" and "NAME".

i have used below expr:-

Week Day =>     " =Date(Floor(weekend(Common_Date)+1),'DD-MMM-YYYY')"

Amount =>      "=sum({<REC_TYPE={BacklogRecHistory},Accounts=,BACKLOG_SBU-={MS},NAME-={'VCAN_OU','VUSA_OU'},Common_Month=>}(BACKLOG_AMT + PAST_DUE_AMT))"

i found that Amount is not correct, it is not showing amount for only "6-Jun-16". it doing sum for that week.

i have added common_date to above table.

Week Day	Common_Date	NAME	VAUS_OU	VCHN_OU	VDX_OU	VEIT_OU	VESA_UO	VFSA_OU	VGMBH_OU	VHK_OU	VLTD_OU
6-Jun-16	30/05/2016		138626	1086332	251191	1887809	2687671	868682	2295839	779307	1528331
	31/05/2016		150937	1087745	230653	1899961	2465266	856833	2289093	767014	1513315
	1/6/2016		119937	1086572	370736	1931176	2392266	833673	2302064	878718	1480480
	2/6/2016		132540	1070785	359580	1924840	2865196	821149	2253630	883580	1454547
	3/6/2016		126236	1041053	349599	1922542	2834879	819777	2289431	885976	1454533
	4/6/2016		126236	1041053	349599	1922542	2834879	819777	2289431	885976	1454533
	5/6/2016		142461	1034452	349599	1914704	2792804	821287	2296596	886595	1496603

Desire result :-

Table should display week start date and amount for that particular date only.

[sunny_talwar · Accepted solution]

Why don't you create a flag for WeekStart dates in the script

If(Floor(WeekEnd(Common_Date)+1) = Common_Date, 1, 0) as Flag

and then use the set analysis in your expression:

{<Flag = {1}>}

[sunny_talwar]

May be like this:

=Sum({<REC_TYPE={BacklogRecHistory}, Accounts=, BACKLOG_SBU-={MS}, NAME-={'VCAN_OU','VUSA_OU'}, Common_Month=, Flag = {1}>}(BACKLOG_AMT + PAST_DUE_AMT))