7 Replies Latest reply: Apr 21, 2015 11:04 AM by Darri Hilmarsson

How to see Revenue exactly one year back in Expression?

Darri Hilmarsson Apr 16, 2015 9:56 AM

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

QV-Example.qvw 152.0 K
QV-Data.xlsx 10.7 K

Re: How to see Revenue exactly one year back in Expression?

Sebastian Lettner Apr 16, 2015 10:09 AM (in response to Darri Hilmarsson)

Hi,

this Expression should work:
sum({$ <OrderDate={">=$(=AddYears(Today(),-1))"}>} Revenue)
Like Show 0 Likes (0)

Actions
- Re: How to see Revenue exactly one year back in Expression?
  
  Darri Hilmarsson Apr 17, 2015 10:08 AM (in response to Sebastian Lettner )
  
  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?
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: How to see Revenue exactly one year back in Expression?
    
    Sebastian Lettner Apr 17, 2015 10:56 AM (in response to Darri Hilmarsson)
    
    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
    
    YearCompare.qvw 162.0 K
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: How to see Revenue exactly one year back in Expression?
      
      Darri Hilmarsson Apr 20, 2015 6:49 AM (in response to Sebastian Lettner )
      
      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)
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: How to see Revenue exactly one year back in Expression?
        
        Sebastian Lettner Apr 20, 2015 9:39 AM (in response to Darri Hilmarsson)
        
        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)
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to see Revenue exactly one year back in Expression?
        
        Darri Hilmarsson Apr 21, 2015 11:04 AM (in response to Sebastian Lettner )
        
        hi, Sebestian
        
        it works like a charm.
        This was exactly what I was looking for.
        Thank very much
        
        Like Show 0 Likes (0)
        
        Actions
Re: How to see Revenue exactly one year back in Expression?

Satish Kurra Apr 20, 2015 7:27 AM (in response to Darri Hilmarsson)

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
Like Show 0 Likes (0)

Actions

Go to original post