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]

Hi,

can you post sample data?

Best regards.

[jonathandienst]

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)

[Anonymous]

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:
Load

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

MasterCalendar:
LOAD
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;

[Anonymous]

I have attached the file with sample data.

[Anonymous]

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

[johanlindell]

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

[Anonymous]

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.

[Digvijay_Singh]

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.

[johanlindell]

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