How to see Revenue exactly one year back in Expression?

[Anonymous]

Hi, QlikView community

I have one issue I wanted to solve.

Here below I have the data. Just a plain sales data.

I am doing an analysis for the SalesRep in my company.

But I got one request from a Sales Rep that I do not know how to solve.

They want to know what their Sum(Revenue) is from this day a year ago until today (one year back).

So I explain this request better. The SalesRep want to know when they open qlikview e.g. on the date 16. april 2015 they can see the Sum(Revenue) from 16. april 2014 until 16. april 2015. And when they open Qlikview next day (17. april 2015) they can see the Sum(Revenue) from 17. april 2014 until 17. april 2015

Any have any ideas?

OrderDate	SalesRep	Item	Revenue
23.1.2014	Kivell	Binder	999,5
9.2.2014	Jardine	Pencil	179,64
26.2.2014	Gill	Pen	539,73
15.3.2014	Sorvino	Pencil	167,44
1.4.2014	Jones	Binder	299,4
18.4.2014	Andrews	Pencil	149,25

[sebastianlettner · Accepted solution]

Hi,

this Expression should work:

sum({$ <OrderDate={">=$(=AddYears(Today(),-1))"}>} Revenue)

[Anonymous]

Thanks Sebastian for this expression.

Now the SalesRep are happy and want to know if they can compare this data.

With the expression you gave me I can see data from 17. april 2014 until 17. april 2015.

The SalesRep want to compare this data with data from 17.april 2013 until 17.april 2014.

What expression should I use if wanted to show Sum(Revenue) from 17.april 2013 until 17.april 2014.

Do you have any ideas?

[sebastianlettner]

Hi,

take a look at the File.

I added a small master calendar. Now if you select a date you get the Revenue for one year back since the selected date. If you select nothing or just a Year or Month the biggest possible date is used as reference.

Now, if you want to see 17.april 2013 to 17.april 2014 just select

Year=2013

Month=April

Day=17

[Anonymous]

Hi, Sebastian

thanks for the respond.

I understand what you are doing and it is good idea.

I have users in my company that find to complicated to qlick and select the a date.

So I am trying to make as simple as I can

But I wanted to compare the data.

So in one column I would have Sum(Revenue) from 17. april 2014 until 17. april 2015

And in another column I would have Sum(Revenue) from 17.april 2013 until 17.april 2014.

Can I make an expression that shows me Sum(Revenue) from 17.april 2013 until 17.april 2014 without selecting a date.

Any ideas?

I was trying this expression but it did not work:

Sum( {$ <Date={">=$(=AddYears(today(), -2))<=$(=AddYears(today(), -1)"}, Year=, Month=, Day=>} Revenue)

[satishkurra]

Also you can refer the similar solution

Sample Data to be loaded in Qlikview

LOAD * Inline [

Date,Product,Sales

01/01/2014,Apple,100

02/01/2014,Samsung,200

03/01/2014,Nokia,900

04/01/2014,Apple,100

05/01/2014,Samsung,200

06/01/2014,Nokia,900

01/01/2015,Samsung,300

02/01/2015,Apple,400

03/01/2015,Nokia,300

03/05/2015,Apple,400

07/01/2015,Samsung,300

08/01/2015,Apple,400

09/01/2015,Nokia,300

10/05/2015,Apple,400 ];

Get Date object to the layout or qlikview presentation layer.

Create a Straight Table, with no dimension and expressions as below

Chart Label1: YTD

Chart Expression1: Sum({$<Date={">=$(=Num(YearStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

Chart Label2: YTD-1

Chart Expression2: SUm({$<Date={">=$(=Num(YearStart(Max(Date),-1)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

Now select some date from Date list box and see the difference or current year to date and previous year to date calculation.

Thanks

Satish

[sebastianlettner]

Hi,

this expression works, you missed a parenthesis.

Sum( {$ <Date={">=$(=AddYears(Today(), -1))<=$(=AddYears(Today(), -2))"}, Year=, Month=, Day=>} Revenue)

But if you don't want to make selections you do not need a calendar. Yust make sure that OrderDate is a date format and you can use this expression:

Sum( {$ <OrderDate={">=$(=AddYears(Today(), -1))<=$(=AddYears(Today(), -2))"}>} Revenue)

[Anonymous]

hi, Sebestian

it works like a charm.

This was exactly what I was looking for.

Thank very much