topic Re: How to calculate Last year Sales in QlikView

How to calculate Last year Sales

Tue, 27 Aug 2013 16:52:31 GMT

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

Re: How to calculate Last year Sales

rajeshvaswani77 — Tue, 27 Aug 2013 16:58:03 GMT

Hi Sujith,

Could you please post a sample QVW file?

thanks,

Rajesh Vaswani

Re: How to calculate Last year Sales

Tue, 27 Aug 2013 19:18:11 GMT

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)

Re: How to calculate Last year Sales

Tue, 27 Aug 2013 19:34:48 GMT

Hi thomas,

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

Thanks

Re: How to calculate Last year Sales

Tue, 27 Aug 2013 20:24:12 GMT

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

Re: Re: How to calculate Last year Sales

Tue, 27 Aug 2013 20:30:24 GMT

Hi Thomas,

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

Thanks.

Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:07:52 GMT

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)

Re: Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:11:32 GMT

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

Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:12:25 GMT

I this something you look for?

Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:18:26 GMT

Use Date as listbox instead of DateID_LY

and use this expression instead:

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

Re: Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:20:36 GMT

The above example gives me sales for Ty in both the columns, i want to have the sales for last year in 2 column.

thanks

Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:35:15 GMT

does this not work:

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

that should show the netdollors for last year

Re: Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:38:24 GMT

Hi thomas,

Sorry it does give this year sales only , Can you plese send me the qvw with last year sales? Maybe i am missing something?

Thanks

Re: Re: Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:44:26 GMT

For me it makes right i think, but maybe im not understanding it properbly

Re: Re: Re: Re: How to calculate Last year Sales

Tue, 27 Aug 2013 21:48:40 GMT

Hi Thomas,

It still gives the same values for TY and LY .I think you might be confused with the expression for this year sales, so i just made it sum of sales and if you see the attached QVW now both of them are same. Can we have such that the second column has the sales for LY?

Thanks

Re: Re: Re: Re: How to calculate Last year Sales

Tue, 27 Aug 2013 22:18:48 GMT

Ohhh i see.

I dont think i can help you, but hope someone can!

As far as i can see - You have a fact tables with ExtDollors on Dates from this Year, related to a dimension with dates from this year, which then relates to dates from last year.

If you try to make two straight tables. One with sum(extDollors) on DateId and one on DateId_Ly you will see that the sum is the same, but on the different dates. Therefore you will always have the same result.

Does your fact table have sums on last year data or do you only have last year in your dimensions?

Look at this: Its the same sum for both, even though im on last year and this year

Re: Re: Re: Re: How to calculate Last year Sales

Wed, 28 Aug 2013 00:01:51 GMT

Hi Thomas,

Sorry actually i limited the sales to greater than 20130801 now i changed it to 20110701 . Can you please take a look at it now and see if we can achieve LY sales

Thanks

Re: How to calculate Last year Sales

Wed, 28 Aug 2013 06:16:14 GMT

hi where did you the attach the new file?

Re: Re: How to calculate Last year Sales

Wed, 28 Aug 2013 12:22:22 GMT

hi Thomas,

Sorry i forgot the attachment, Here it is

Thanks

Re: Re: How to calculate Last year Sales

Wed, 28 Aug 2013 12:23:19 GMT

Hi thomas,

Sorry i forgot the attachment, Here it is.

thanks