topic Re: Calculating sales to the date a user select in QlikView

Calculating sales to the date a user select

Thu, 13 Sep 2012 15:51:55 GMT

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.

Re: Calculating sales to the date a user select

v_iyyappan — Thu, 13 Sep 2012 19:33:51 GMT

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

Re: Calculating sales to the date a user select

Mon, 24 Sep 2012 08:22:32 GMT

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