
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Thanks in advance.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.:)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
can you post sample data?
Best regards.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have attached the file with sample data.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I mean a qvd, anyway the best way is the mastercalendar, I think.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That's funny, because it works on my laptop / QlikView installation.

- « Previous Replies
-
- 1
- 2
- Next Replies »