Compare a measure (sales) for an attribute (date)

[#1]

Hi

I want to create a chart that allows a user to compare sales for two dates (current and previous) across region and country. Furthermore the user wants the freedom to change the current and previous dates. If my data looks like this:

Region	Country	SalesDate	Sales
Asia	Australia	1/08/2015	105
Asia	Australia	2/08/2015	87
Asia	Australia	3/08/2015	34
Asia	Australia	4/08/2015	200
Asia	New Zealand	1/08/2015	25
Asia	New Zealand	2/08/2015	56
Asia	New Zealand	3/08/2015	23
Asia	New Zealand	4/08/2015	54
Asia	Japan	1/08/2015	654
Asia	Japan	2/08/2015	45
Asia	Japan	3/08/2015	432
Asia	Japan	4/08/2015	645
Europe	Germany	1/08/2015	321
Europe	Germany	2/08/2015	432
Europe	Germany	3/08/2015	867
Europe	Germany	4/08/2015	534

and the user selects 1/8/2015 as the previous date and 3/8/2015 as current date the chart will look something like this:

Sales		Previous	Current	Difference
Asia	Australia	105	34	-71
	New Zealand	25	23	-2
	Japan	654	432	-222
	subtotal	784	489	-295
Europe	Germany	321	867	546
	subtotal	321	867	546
Total		1105	1356	251

and if the user selects 2/8/2015 as the previous date and 3/8/2015 as current date the same chart will look something like this

Sales		Previous	Current	Difference
Asia	Australia	87	34	-53
	New Zealand	56	23	-33
	Japan	45	432	387
	subtotal	188	489	301
Europe	Germany	432	867	435
	subtotal	432	867	435
Total		620	1356	736

I've been trying using a combination of

set analysis (for previous sales I have "=sum({<SalesDate={"=Date($(vPriorComparisonDate),'DD-MM-YYYY')"}>}Sales)"

a variable (vPriorComparisonDate) set by means of a calendar object (for user to select previous date)

a list box showing SalesDate (for user to select current date)

either a pivot table or a straight table

But values are not correct

I've attached the excel mockup for ease of reference

Any help would be much appreciated.

Thanks

Ross

[#2]

I made some progress using the following expression

sum({$<Category={'SwapCurveRisk'}>}SpcValue)

this worked perfectly until i introduced alternate states. i set sheet A to StateA. all the objects on the sheet to inherited. the other sheet (B) i set to StateB, again all objects on that sheet to inherited. reason being on sheet A users must select a ReportingDate (always one selected value=yes). sheet b i want  users to be able to perform time series analysis - e.g. sum(SpcValue) by Category across time (ReportingDate )

now, for some reason, the expression above is ignoring the ReportingDate selection on sheet A - it just sums up all the values no matter the ReportingDate selected.

please help

[sunny_talwar]

Is your ReportingDate Filter in StateA or InheritedState? If its in StateA, you can try this:

Sum({$<Category={'SwapCurveRisk'}, ReportingDate = StateA:: ReportingDate>}SpcValue)

[swuehl · Accepted solution]

Not really sure if you want to mix default state with alternate states in your charts, but set identifier $ is referencing default state, so if you want the chart to consider only State A and the chart is set to inherited state and the sheet to state A, you need to remove the set identifier:

=sum({<Category={'SwapCurveRisk'}>}SpcValue)