How to calculate Last year Sales

[#1]

Hi All,

I am building a Qlikview App and i have to do TY Sales Vs LY Sales for a month .To calculate LY sales i have created 2 new variables as

vmaxDateIDLY = DateId_LY ( which gives the last year date)

vLY_CurrDate = [Fiscal Year]=, [Fiscal Qtr]=, [Fiscal Half]=, [Fiscal Month]=, [Week End]=,Date=, [Fiscal Week]=, [FiscalWeekDesc]=, DateId={$(vmaxDateIDLY)}

And inside the expression i have something like this

sum({$<$(vLY_CurrDate)>}Sales) .

The output i get is i see a new row in the bottom for the LY date of current date with the sales and remaining all dates as zero's.However if i pick a single date then i get a new row on the top of select date which has last year date and sales .

How do i get the LY sales in the same row rather than getting a new row?

Thanks

[rajeshvaswani77]

Hi Sujith,

Could you please post a sample QVW file?

thanks,

Rajesh Vaswani

[#3]

Use these variables:

LET vDateToday = num(Date(Today(), 'YYYY-MM-DD'));

LET vThisYear = num(Year(vDateToday));

LET vLastYear = vThisYear - 1;

2012 will be:

sum({$<Datefield={$(vLastYear)}>}Sales)

2013 will be:

sum({$<Datefield={$(vThisYear)}>}Sales)

[#4]

Hi thomas,

I made these changes but i see zero's inside the values?

Thanks

[#5]

check if ur date is YYYY-MM-DD otherwise change it.

I cannot open ur qlikview file. Can u reduce it so its not a 100mb file?

click file in menu and reduce data

[#6]

Hi Thomas,

Attached is my reduced QVW , Can you please look into it and let me know.

Thanks.

[#7]

okay first of all:

I am a bit confused on what you want. Do you want to show a row for last year sales and a row for this years sales where it is YTD and LYTD ?

If you want that, ill recommend you to use a mastercalender like this:

///******Create min/max variables*********

Let vMinDate = num(peek('Date_Clean', 0, 'DimDate'));

Let vMaxDate = num(peek('Date_Clean', -1, 'DimDate'));

Let vMaxDatetest = peek('Date_Clean', -1, 'DimDate');

Let vToday = num(Today());

//Let vToday = vMaxDate;

////*******Temp Calendar******

DateField:

Load

    date($(vMinDate) + RowNo() -1) as TempDate

AutoGenerate

    $(vMaxDate) - $(vMinDate) +1;

//   

//   

//   

//  

////       

////     Date($(vMinDate) + RowNo() - 1) AS TempDate

////     AUTOGENERATE

////     $(vMaxDate) - $(vMinDate) +1;

//    

////    

////     Date($(vDateMin) + RowNo() - 1) AS TempDate

////     AUTOGENERATE 1

////     WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

////   

MasterCalendar:

Load

TempDate as Date_Clean,

Week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

Weekday(TempDate) as WeekDay,

'Q' & ceil(month(TempDate) / 3) as Quarter,

Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

Week(TempDate) & '-' & Year(TempDate) as WeekYear,

inyeartodate(TempDate, $(vToday),0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(vToday),-1) * -1 AS LastYTDFlag,

inyear(TempDate, $(vToday), 0) * -1 AS CY, //Current year

  inyear(TempDate, $(vToday), -1) * -1 AS FPY, //First prior year

  inyear(TempDate, $(vToday), -2) * -1 AS SPY, //Second prior year

// Year-to-date flags

  inyeartodate(TempDate, $(vToday), 0) * -1 AS CYTD, //Current year-to-date

  inyeartodate(TempDate, $(vToday), -1) * -1 AS FPYTD, //First prior year-to-date

  inyeartodate(TempDate, $(vToday), -2) * -1 AS SPYTD, //Second prior year-to-date

// Quarter flags

  inquarter(TempDate, $(vToday), 0) * -1 AS CQ, //Current quarter

  inquarter(TempDate, $(vToday), -4) * -1 AS FPQ, //First prior quarter, same quarter last year

  inquarter(TempDate, $(vToday), -8) * -1 AS SPQ, //Second prior quarter, same quarter two years ago

// Quarter-to-date flags

  inquartertodate(TempDate, $(vToday), 0) * -1 AS CQTD, //Current quarter-to-date

  inquartertodate(TempDate, $(vToday), -4) * -1 AS FPQTD, //First prior quarter-to-date, same quarter last year

  inquartertodate(TempDate, $(vToday), -8) * -1 AS SPQTD, //Second prior quarter-to-date, same quarter two years ago

// Month flags

  inmonth(TempDate, $(vToday), 0) * -1 AS CM, //Current month

  inmonth(TempDate, $(vToday), -1) * -1 AS PM, //Previous month

  inmonth(TempDate, $(vToday), -12) * -1 AS FPM, //First prior month, same month last year

  inmonth(TempDate, $(vToday), -24) * -1 AS SPM, //Second prior month, same month two years ago

// Month-to-date flags

  inmonthtodate(TempDate, $(vToday), 0) * -1 AS CMTD, //Current month-to-date

  inmonthtodate(TempDate, $(vToday), -1) * -1 AS PMTD, //Previous month-to-date

  inmonthtodate(TempDate, $(vToday), -12) * -1 AS FPMTD, //First prior month-to-date, same month last year

  inmonthtodate(TempDate, $(vToday), -24) * -1 AS SPMTD, //Second prior month-to-date, same month two years ago

  MonthStart(TempDate, 'YYYY-MM-DD') as MS,

  date(MonthStart(TempDate), 'YYYY-MM-DD') as MS2,

// Week flags (use addmonths() to ensure proper week)

// Use inlunarweek() if the you want weeks (7-day periods) starting from January 1. This is different from the inweek() function

  inweek(TempDate, $(vToday), 0) * -1 AS CW, //Current week

  inweek(TempDate, addmonths($(vToday), -12), 0) * -1 AS FPW, //First prior quarter, same week last year

  inweek(TempDate, addmonths($(vToday), -24), 0) * -1 AS SPW, //Second prior quarter, same week two years ago

// Week-to-date flags (use addmonths() to ensure proper week)

// Use inlunarweek() if the you want weeks (7-day periods) starting from January 1. This is different from the inweek() function

  inweektodate(TempDate, $(vToday), 0) * -1 AS CWTD, //Current week-to-date

  inweektodate(TempDate, addmonths($(vToday), -12), 0) * -1 AS FPWTD, //First prior week-to-date

  inweektodate(TempDate, addmonths($(vToday), -24), 0) * -1 AS SPWTD, //Second prior week-to-date

// Day flags (use addmonths() to ensure proper day)

  inday(TempDate, $(vToday), 0) * -1 AS CD, //Current day

  inday(TempDate, addmonths($(vToday), -12), 0) * -1 AS FPD, //First prior quarter, same day last year

  inday(TempDate, addmonths($(vToday), -24), 0) * -1 AS SPD, //Second prior quarter, same day two years ago

// Additional Week Flags

  inweek(TempDate, $(vToday), -1) * -1 AS W1, //One week ago

  inweek(TempDate, $(vToday), -2) * -1 AS W2, //Two weeks ago

  inweek(TempDate, $(vToday), -3) * -1 AS W3, //Three weeks ago

  inweek(TempDate, $(vToday), -4) * -1 AS W4, //Four weeks ago

  inweektodate(TempDate, $(vToday), -1) * -1 AS W1TD, //One week ago to date

  inweektodate(TempDate, $(vToday), -2) * -1 AS W2TD, //Two weeks ago to date

  inweektodate(TempDate, $(vToday), -3) * -1 AS W3TD, //Three weeks ago to date

  inweektodate(TempDate, $(vToday), -4) * -1 AS W4TD //Four weeks ago to date

RESIDENT DateField

Order by TempDate ASC;

DROP table DateField;

Second of all:

You need to change data format in main tab to MM/DD/YYYY

third you could do something like this:

vDateToday = =Date(Today()) - This will give you the date today

vFirstYearDate = Min(Date) - This will give you the first date in the dataset (but its only because you have dates in this year)

vYTD = ='>=$(vFirstYearDate) <=$(vDateToday)

[#8]

Hi Thomas,

I dont want a new row for TY sales and LY sales, If you see the column named FP net sales rtl val,it has the values for current year and the next column LY FP Net sales must have the sales for last year beside the this year sales.

Thanks

[#9]

I this something you look for?

[#10]

Use Date as listbox instead of DateID_LY

and use this expression instead:

=sum({$<DateId_Ly=>} ExtNetDollars)