Issue in set analysis

[lionking15]

  Find below sample data

ID	Number	Date	Amount
1	0	5/6/2016	10
1	0	8/6/2016	10
1	0	4/5/2017	10
1	1	5/5/2017	10
2	0	25/6/2017	10
2	0	15/8/2017	10
2	0	25/8/2017	10

For Example If users select a date'15/8/2017'then first i have to see whether "ID"  fall under same fiscal year(Within 1st april to 31st march)

Output-(Not Needed)

ID	Number	Amount
1	0	10
1	1	10
2	0	30

My Requirement-

ID	Number	Amount
1	0	30
1	1	10
2	0	30

Scenario-For ID=1 we can see that 4/5/2017 and 5/5/2017 fall under this fiscal year and my output Value should search whether Number is falling under previous Fiscal year,Son on basis of Number i will bring previous year Amount in front of ID.

How can i achieve this within set analysis.

[effinty2112]

Hi Giridahar,

Add this line to your script:

if (Ceil(Month(Date) / 3)=1,Year(Date)-1 & '/' & Year(Date), Year(Date) & '/' & Text(Year(Date)+1) ) as [Fiscal Year],

Selecting on Fiscal Year is now easy. Set analysis not required.

If you can't access the script you could add

if (Ceil(Month(Date) / 3)=1,Year(Date)-1 & '/' & Year(Date), Year(Date) & '/' & Text(Year(Date)+1) )

as a calculated dimension in charts:

Fiscal Year	ID	Number	sum(Amount)
2016/2017	1	0	20
2017/2018	1	0	10
		1	10
	2	0	30

Regards

Andrew

[lionking15]

I don't want fiscal year as my dimension.. And user will select a date on basis which value has to be calculated