topic Re: Table expression, Set analysis filter on Month and Day? in QlikView

Table expression, Set analysis filter on Month and Day?

Tue, 20 Jan 2015 09:39:33 GMT

Hi,

I have a new problem.

I want to write an expression to make a sum of values. But i only want to sum records where Months and Days are <= than current Month and current day.

For exemple :

Imagine that current date is : 20/01/2014

Day = 20

Month = 01

Date	Day	Month	Do sum?
01/01/2012	01	01	Yes
10/01/2013	10	01	Yes
20/01/2013	20	01	Yes
28/01/2013	28	01	No
15/01/2014	15	01	Yes
19/03/2014	19	03	No
20/11/2014	20	11	No
02/01/2015	02	01	Yes
15/01/2015	15	01	Yes
20/01/2015 (Today)	20	01	Yes

The objectif is to compare sums of values from the current day with sums of values from others years but at the same date.

I tried this, but it does not work : =Sum({$<Mouvement={"Livraison"}, Month={"<=$(vCurrentMonth)"}, Day={"<=$(vCurrentDay)"}>}Val)

Do you have an idea?

Thanx for your help.

Re: Table expression, Set analysis filter on Month and Day?

alexandros17 — Tue, 20 Jan 2015 09:45:30 GMT

Define a variable in the script :

LET myDate = Month(Today())*10 + Day(Today());

When you load your data, create a new field like this:

load

...

Month(myDate)*10 + Day(myDate) as MyMonthDayValue

...

resident myTable

Now you have a field to use in your expression

Sum({$<Mouvement={"Livraison"}, MyMonthDayValue={'<=$(myDate )'}>}Val)

let me know

Re: Table expression, Set analysis filter on Month and Day?

Tue, 20 Jan 2015 09:58:19 GMT

Ok, i understand, but i want to know why you do *10 (Month(Today())*10) ?

Re: Table expression, Set analysis filter on Month and Day?

alexandros17 — Tue, 20 Jan 2015 10:11:24 GMT

... it's a mistake, multiply by 100:

if Month is 3 (March) and day is 4 then you have 3 * 100 + 4 = 304

if Month is 3 (March) and day is 21 then you have 3 * 100 + 21 = 321

if Month is 11 (november) and day is 4 then you have 11 * 100 + 4 = 1104

if Month is 11 (november) and day is 21 then you have 11 * 100 + 21 = 1121

You have a natural sorting concatenating month and day

Re: Table expression, Set analysis filter on Month and Day?

Tue, 20 Jan 2015 15:12:38 GMT

Ok,

That's what I thought.

Thanx for your help !!!