topic Re: How to create Sales Last year to Date in QlikView

How to create Sales Last year to Date

Wed, 30 Dec 2015 11:50:17 GMT

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

Re: How to create Sales Last year to Date

Anonymous — Wed, 30 Dec 2015 12:54:40 GMT

I think, yes...

Could you share the expression for Sales LY??

Re: How to create Sales Last year to Date

Clever_Anjos — Wed, 30 Dec 2015 13:12:21 GMT

StartDate and EndDate are variables or fields?

Re: How to create Sales Last year to Date

Anonymous — Wed, 30 Dec 2015 13:19:08 GMT

Using combination of Date(),Addyears(), or Addmonths() we can achieve same

Re: How to create Sales Last year to Date

HirisH_V7 — Wed, 30 Dec 2015 13:32:23 GMT

Hi,

Check this

https://community.qlik.com/docs/DOC-5103‌

Hope this helps,

Hirish

Re: How to create Sales Last year to Date

Wed, 30 Dec 2015 14:12:34 GMT

Hi,

Hope this link will help

Sum between two dates

Expression:

Sum( {$<DateField = {“>=$(=Min(StartDate))<=$(=Max(EndDate))”} >} Sales LY)

Re: How to create Sales Last year to Date

Thu, 31 Dec 2015 09:07:20 GMT

Hello Balray,

This is the currenct expression for Sales LY :

sum({<Year={$(=max(Year)-1)}>}SalesPrice*TransactionQuantity)/1000

Re: How to create Sales Last year to Date

Thu, 31 Dec 2015 09:08:28 GMT

variables

Re: How to create Sales Last year to Date

Thu, 31 Dec 2015 09:44:38 GMT

Hi Guys,

none of your answers above has solved the problem. It;s kinda urgent.

Is there anybody who are able to do a screen sharing session today? i have no prefer using tools such as teamviewer/Skype etc.

Waiting for your answer.

Greets,

Rega

Re: How to create Sales Last year to Date

Clever_Anjos — Thu, 31 Dec 2015 11:29:28 GMT

Since they´re variables you don´t need to worry about Max() and Mins()´s, onlky care about formatting values

This should be enough

sum({<YourDateField={">=$(=Date(StartDate)) <= "$(=Date(EndDate))" "}>}SalesPrice*TransactionQuantity)/1000

Re: How to create Sales Last year to Date

sasikanth — Thu, 31 Dec 2015 11:38:50 GMT

Please Share your application if possible

Re: How to create Sales Last year to Date

Anonymous — Thu, 31 Dec 2015 11:47:26 GMT

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

Re: How to create Sales Last year to Date

Thu, 31 Dec 2015 12:53:31 GMT

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

Re: How to create Sales Last year to Date

koushik_btech20 — Thu, 31 Dec 2015 13:49:11 GMT

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.

Re: How to create Sales Last year to Date

Thu, 31 Dec 2015 14:49:01 GMT

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.

Re: How to create Sales Last year to Date

Anonymous — Thu, 31 Dec 2015 15:33:31 GMT

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)

Re: How to create Sales Last year to Date

sunny_talwar — Thu, 31 Dec 2015 15:48:22 GMT

Would you be able to share your QVW file?

Re: How to create Sales Last year to Date

koushik_btech20 — Mon, 04 Jan 2016 12:44:47 GMT

Hi Rega,

Can u share your QVW otherwise its difficult to resolve.

Regards,

Koushik

Re: How to create Sales Last year to Date

Mon, 04 Jan 2016 15:45:23 GMT

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

Re: How to create Sales Last year to Date

oknotsen — Mon, 04 Jan 2016 15:48:20 GMT

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