Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI
Please Share your application if possible
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
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
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.
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.
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)
Would you be able to share your QVW file?
Hi Rega,
Can u share your QVW otherwise its difficult to resolve.
Regards,
Koushik
Sunny T, what is your e-mailadress which i can send the QVW file?
Just attach it to your post . If you click on "Use advanced editor" you get an attach option.