13 Replies Latest reply: Jul 29, 2015 11:01 AM by Uday P

# Sum of Sales YTD

)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.

• ###### Re: Sum of Sales YTD

Hi,

can you post sample data?

Best regards.

• ###### Re: Sum of Sales YTD

I have attached the file with sample data.

• ###### Re: Sum of Sales YTD

I mean a qvd, anyway the best way is the mastercalendar, I think.

• ###### Re: Sum of Sales YTD

Perhaps these two expressions:

For selected year:

Sum(<Date = {">=\$(=YearStart(Max(Date))) <=\$(=Max(Date))"}>} Sales)

For previous year:

Sum(<Date = {">=\$(=YearStart(Max(Date), -1)) <=\$(=AddYears(Max(Date), -1))"}>} Sales)

• ###### Re: Sum of Sales YTD

Only an idea, and using a flag in a mastercalendar?

For example this:

let varMinDate = num(peek('DATEFT',0,'Facts'));
let varMaxDate = num(peek('DATEFT',-1,'Facts'));

TempCalendar:

date(\$(varMinDate) + rowno() -1) as TempDate
autogenerate \$(varMaxDate) - \$(varMinDate) +1;

MasterCalendar:
date(TempDate) AS DATEFT,
week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
inyeartodate (TempDate,Date(Today()), 0) *-1 as CYTDFlag, //FROM START OF YEAR TO TODAY
inyeartodate (TempDate,Date(Today()), -1) *-1 as LYTDFlag, //FROM START OF PREVIUOS YEAR TO SAME DATE OF PREVIUOS YEAR
inyear (TempDate,Date(Today()), 0) *-1 as CYFlag,
inyear (TempDate,Date(Today()), -1) *-1 as LYFlag,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
'T' & ceil(month(TempDate)/3) as Quarter,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
weekday(TempDate) AS WeekDay

RESIDENT
TempCalendar
ORDER BY
TempDate Asc;

DROP TABLE TempCalendar;

• ###### Re: Sum of Sales YTD

Hi Uday.

Here's an example how you can create the formulas in set expression. One thing to think about I usually find is the extra Date around Max (Date). The Max(Date) often returns a number that you need to convert back to a date format for the set expression to work.

Sum ({<Date = {'>=\$(=YearStart (Date(Max(Date))))<=\$(=Date(Max(Date)))'}, Month =, Day =>} Sales)

//Johan

• ###### Re: Sum of Sales YTD

Hi Johan,

The expression you have given is showing only the sales of selected day and month for current and previous years, but not the summation of sales from starting of the year to the selected date.

• ###### Re: Sum of Sales YTD

You can try using YearToDate Function -

YTD                 -Sum(Sales * YearToDate(Date, 0, 4, vYEDate))

LYTD                -Sum(Sales * YearToDate(Date, -1, 4, vYEDate))

Have a look at dev cookbook page 200, may resolve your problem with less coding.

• ###### Re: Sum of Sales YTD

That's funny, because it works on my laptop / QlikView installation.

• ###### Re: Sum of Sales YTD

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

• ###### Re: Sum of Sales YTD

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

• ###### Re: Sum of Sales YTD

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.

• ###### Re: Sum of Sales YTD

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)