Sum of Sales YTD

[Anonymous]

)Hello Everyone,

I am trying to perform the sum of sales operation using set analysis in which when only the month and day are selected from two separate list boxes, the sum of sales for current year and the previous years starting date(i.e. 1/1/2015 and 1/1/2014) to the selected day and month are to be summed up and shown. For example if month =July and Day=27, the sum of sales from 1/1/2015 to 27/07/2015 and 1/1/2014 to 27/07/2014 are to be displayed. The Dimensions are Country and year. The expressions tried so far are

=aggr(sum({<Date={">=$(vStartDate)<=$(vEndDate)"}>}Sales),Country,Year)

=aggr(sum(Sales),Country, Year)

=sum({<Date={">=$(vStartDate)<=$(vEndDate)"}Country=, Year=>}Sales)

=Sum({$<Date= {'>$(=Max((YearStart(Date1)))) <=$(=Max(Date1))'}>} Sales ).

I have used these same formulae in "if" condition to get current and previous year sales. Also I have used the same formula for Billing Days and it is working fine. Please Help.

Thanks in advance.

[Anonymous]

The same thing is happening with all the sum of sales set analysis techniques I have tried with.

[Anonymous]

I have been working with only the Day and Month listboxes as inputs but not the year because the requirement is in such a way. Could that be affecting the resultant output

[johanlindell]

Hi,

Have another look at the application. Now it has a "full" calendar so that you don't select a month/day that only has data for 2014. Looks fine in my QlikView 11.2 SR11.

I also added a chart with monthly aggregated sales figures to validate that the data is correct.

[Anonymous · Accepted solution]

Found the solution

For Current year sales:

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

For previous year sales:

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum), -1)))<=$(=AddYear(Max(DateNum), -1))"}>} Sales)

Thank you everyone for your time and your help.:)