How to create Sales Last year to Date

[#1]

Hello all,

In the red markered column in straight table there's no Sales data. I expected in this column sales data based on  startdate 01-12-2014  and enddate 31-12-2014).

Which set analysis / script codes should i use to get the data in the red markered column?

Thank you very much.

Greets,

Rega

[sasikanth]

HI

Please Share your application if possible

[Anonymous]

Do sth like this:

VlastYearStartDate=addyears($(VStartDate), -1)

VlastYearLastDate=addyears($(VLastDate), -1)

then use this expression:

=sum({<YourDateField=,YourDateField={">=$(=VlastYearStartDate) <= "$(=VlastYearLastDate)" "}>}SalesPrice*TransactionQuantity)/1000

Make sure format should be same

[#4]

Hey Balraj,

the problem is not solved.

This is what i have done:

I created the two new variables : vlastyearstartdate en vlastyearlastdate (see image below)

I putted your expression  (see image below)

What did i wrong?

Thanks

[koushik_btech20]

Please follow below expression and use,

sum({<DATE={'>=$(vLYStartDate)<=$(vLYEndDate)'},Year,Month,Quarter,Day>}SalesPrice*TransactionQuantity)

If that expression is not working then check the date format in your application.

[#6]

Hello Koushik,

As you can see (in the image below), i still cant see the difference in results of Sales 15-01-2015 until 16-01-2015

and Sales 15-01-2014 until 16-01-2014.

[Anonymous]

Rega,

An idea you can try is to utilize a series of FLAGS in your script.  I use the following FLAGS to accomplish year over year sales calculations.  See below:

LET vTodaysDate = Num (Today());

TempCal1:
LOAD Distinct
Num(Max(Year)) as MaxYear
FROM
[QVDs\WMCalendar.qvd]
(qvd);

LET vMaxYear = Num(Peek('MaxYear', 0, 'TempCal1'));

TempCal2:
LOAD Distinct
If(($(vTodaysDate)-DateNum)>7 and ($(vTodaysDate)-DateNum)<14, Week) as CW

FROM
[QVDs\WMCalendar.qvd]
(qvd);

LET vCW = Num(Peek('CW', -1, 'TempCal2'));

DROP Table TempCal1;
DROP Table TempCal2;

[Calendar Dimension]:
LOAD DateNum,
Date,
Year,
Quarter,
Month,
MonthNum,
Week,
YearWeek,
YearWeek as [Customer Year Week], //JOINING POS FACT TABLE ON THIS FIELD

//Current Year Flags
  If($(vCW)-Week=0 and Year=$(vMaxYear), 1, 0) as CWTD,
If($(vCW)-Week<=3 and $(vCW)-Week>=0 and Year=$(vMaxYear), 1, 0) as CY4WTD,
If($(vCW)-Week<=7 and $(vCW)-Week>=0 and Year=$(vMaxYear), 1, 0) as CY8WTD,
If($(vCW)-Week<=11 and $(vCW)-Week>=0 and Year=$(vMaxYear), 1, 0) as CY12WTD,
If($(vCW)-Week<=51 and $(vCW)-Week>=0 and Year=$(vMaxYear), 1, 0) as CYTD,

//Last Year Flags
  If($(vCW)-Week=0 and Year=$(vMaxYear)-1, 1, 0) as LWTD,
If($(vCW)-Week<=3 and $(vCW)-Week>=0 and Year=$(vMaxYear)-1, 1, 0) as LY4WTD,
If($(vCW)-Week<=7 and $(vCW)-Week>=0 and Year=$(vMaxYear)-1, 1, 0) as LY8WTD,
If($(vCW)-Week<=11 and $(vCW)-Week>=0 and Year=$(vMaxYear)-1, 1, 0) as LY12WTD,
If($(vCW)-Week<=51 and $(vCW)-Week>=0 and Year=$(vMaxYear)-1, 1, 0) as LYTD,

//Last 2 Year Flags (2 years ago)
  If($(vCW)-Week=0 and Year=$(vMaxYear)-2, 1, 0) as L2WTD,
If($(vCW)-Week<=3 and $(vCW)-Week>=0 and Year=$(vMaxYear)-2, 1, 0) as L2Y4WTD,
If($(vCW)-Week<=7 and $(vCW)-Week>=0 and Year=$(vMaxYear)-2, 1, 0) as L2Y8WTD,
If($(vCW)-Week<=11 and $(vCW)-Week>=0 and Year=$(vMaxYear)-2, 1, 0) as L2Y12WTD,
If($(vCW)-Week<=51 and $(vCW)-Week>=0 and Year=$(vMaxYear)-2, 1, 0) as L2YTD,

//Current Retail Year Flags
If(Year=$(vMaxYear), 1, 0) as CYEAR,
If(Year=$(vMaxYear)-1, 1, 0) as LYEAR,
If(Year=$(vMaxYear)-2, 1, 0) as L2YEAR,
If(Year=$(vMaxYear)-3, 1, 0) as L3YEAR

FROM
QVDs\Calendar.qvd
(qvd)

EXPRESSIONS:

Sum(Sales*CYTD)

Sum(Sales*LYTD)

[sunny_talwar]

Would you be able to share your QVW file?

[koushik_btech20]

Hi Rega,

Can u share your QVW otherwise its difficult to resolve.

Regards,

Koushik

[#10]

Sunny T, what is your e-mailadress which i can send the QVW file?

[oknotsen]

Just attach it to your post . If you click on "Use advanced editor" you get an attach option.