Calculating sales to the date a user select

[#1]

Hi,

I'm struggling with what I believe should really be a straight forward issue and need some help.

I want to calculate what the sales are upto and including a date I select from the date dimensions.  I also want the other dimensions open i.e. so I can select a specific product or region, etc and see it's sales to date.  At the moment I have sale to date using set analysis and the {1} however this is fixing the date and ignoring all other selections made.  I want to fix my date and let the user select the other dimensions.

In the attached example I have named varibles for the date that my data begins i.e. vStart and the last day of the selected month i.e. vEnd.

Please could some one help?

Many thanks in advance.

C

[v_iyyappan]

Hi, 

In script u should add

Num([Transaction Date]) AS SalesNumDate,

Day([Transaction Date]) As Day

Using the expression like this

= Sum({<Year=,Month=, SalesNumDate={'>=$(=(Num(MonthStart(vSelectedDate))))<=$(=(Num(MonthEnd(vSelectedDate))))'}>} Sales)

Where vSelectedDate = =MakeDate(Max(Year),Max(Month),Max(Day))

Hope its helps

Regards,

Iyyappan

[#3 · Accepted solution]

I got there in the end with a mixture of a few suggestions and research

=sum({<[Transaction Date] = {"<=$(=max([Transaction Date]))"}, Year, Month>}Sales)

Thanks

C