Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cliff_clayman
Creator II
Creator II

Comparing Current Year to Date to Last Year Sales Figures

I am looking to do a Sales dollars comparison for Current Year to Date to Last Year.  I would like to compare our current month sales figures to that of last years sales for the same month.  I already have a variable set up for vFY (fiscal year), vCY (current year) and vLY (last year) and one for vCM (Current Month).  How can I sum last years sales for the same month as this years current month?  Here is what I have thus far:

Current Year: Sum(If(vFY = vCY, [Sales])

Last Year:     Sum(If(vFY = vLY and Month = vCM, [Sales])

How can I Sum up all the months up to the current month for last year?  I don't need to do that for current year as I only have the data through the current month.

3 Replies
cliff_clayman
Creator II
Creator II
Author

I can simplify this a little further if it helps.  Can someone just tell me how to get the Sum of Months 1-6 for last year?  I can't even seem to do that.  How can I set a variable that has multiple values or somehow loop through the months that I am looking for?

vishsaggi
Champion III
Champion III

May be this ?

Sum({<DateField = {"$(= '>=' & AddMonths(MonthStart(Today()), -12)  & '<=' & AddMonths(MonthStart(Today()), -6)"}>}Sales)

effinty2112
Master
Master

Hi Cliff,

               I definitely think that the easiest way to do this is by adding a YTD flag toyour calendar

Something like this:

Let vStartDate = floor(date('XX/XX/XXXX')); // Put your own start date here

Let vToday = Floor(Today()); // Of course you can put a future date here if appropriate

Calendar:

.

.

if(DayNumberOfYear([Temp Date])<=DayNumberOfYear($(vToday)),1,0)  as YTD,

.

.

;

Load $(vStartDate) -1 + IterNo() as [Temp Date]

AutoGenerate 1 While $(vStartDate) -1 + IterNo() <= $(vToday);

Now you can use the set modifier <YTD = {1}> to pick out values from previous years that you can compare to the current year or simply selecting 1 in a YTD listbox will show previous years' totals to the same day in this year.

Here's the extract from QV help on the DayNumberOfYear function:

DayNumberOfYear(date[,firstmonth])

Returns the day number of the year according to a timestamp with the first millisecond of the first day of the year containing date. The function always uses years based on 366 days.

By specifying a firstmonth between 1 and 12 (1 if omitted), the beginning of the year may be moved forward to the first day of any month. If you e.g. want to work with a fiscal year starting March 1, specify firstmonth = 3.

Examples:

DayNumberOfYear(date) returns the day number counted from the first of the year.

DayNumberOfYear(date,3) returns the number of the day as counted from the first of March.

Hope it helps.

Kind regards

Andrew