topic Re: Expression Problem in QlikView

Expression Problem

Wed, 04 Mar 2015 13:02:46 GMT

Dear Fellow Users,

I am struggling with an expression issue those days, maybe some of you could have a solution for me.

In my job, people book a service that they use later on.

I would like to compare what I have on the books today to what I had on the books last year at the same time.

The field “BookDate” tells me the date of the reservation

I have in a chart those expressions:

The Revenue I have on the books this year: SUM(ProdRev)

The revenue I generated last year: SUM({$<Year_Name={$(=MAX(Year_Name)-1)}>}ProdRev)

Basically, I would like to have the revenue I generated last year for all values of "BookDate" that are below (today-364). Any Idea of how I could do that ?

Re: Expression Problem

hic — Wed, 04 Mar 2015 16:19:00 GMT

Take a look at

Year-over-Year Comparisons

and

Calculating rolling n-period totals, averages or other aggregations

You can also search the community for threads with "Year-To-Date"

HIC

Re: Expression Problem

Thu, 05 Mar 2015 15:20:25 GMT

Hi Henric,

Thanks for your answer. However, this is not exactly what I meant.

Let’s say you are managing a transportation company.

3 things will be interesting for you:

1. Revenue you have on the books for next days/months, for me would be sum(ProdRev), Bleu line in the Graph.

2. How you performed last Year for me : SUM({$<Year_Name={$(=MAX(Year_Name)-1)}>}ProdRev) This is what you call YoY. Orange Line in the Graph.

3. What you had on the books last year at the same time (bookings made before March 05 2014 for last year), Grey line in the Graph

Basically, what I would like is an expression that says : ”SUM({$<Year_Name={$(=MAX(Year_Name)-1)}>}ProdRev) for BookDate<today-365”.

Have a nice day !

Re: Expression Problem

yura_ratu — Fri, 06 Mar 2015 15:44:47 GMT

As I undertand you have two dates - booking date and date of using transport service, wright? Could you give an example of data you have

Re: Expression Problem

hic — Fri, 06 Mar 2015 16:38:58 GMT

You say that you want a condition like BookDate<today-365. This is exactly what I define in the blog post Year-over-Year Comparisons in the formula

If( DayNumberOfYear(Date) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,

Here you get a Boolean field that says whether the date is before the same time of the year as today.

So, all I think you need to do, is to define such a field in the script, based on your booking date, and then use this field in a Set Analysis expression, for example

Sum( {$<Year_Name={$(=MAX(Year_Name)-1)}, IsInYTD={1}>} ProdRev )

HIC