topic function below(sum(x),A,B) make all arguments calculated in QlikView

function below(sum(x),A,B) make all arguments calculated

Fri, 03 Jun 2011 11:48:13 GMT

Hello there,

my first thread on this community - pls be patient if I,m not precisely thx

im trying to get things work with this function. In QV help is written: below(sum(value1),1,3) lets you look back to previous 3 rows on load statement and sum value1 from this previous 3 row and insert this sum to current row.

Argument "3" is static. Ist there any trick to make this argument calculable in my case depending on values in previous rows?

Let say we have a table with dates sorted ascending but they appear not day after day. For each day we have no. of sold cars.

date	no. Sold cars
2011-04-01	2
2011-04-03	4
2011-04-06	1
2011-06-07	5
2011-06-08	4
2011-06-13	3
2011-06-15	2
2011-06-16	4
2011-06-19	5

based on this table i`d like to add 2 extra columns that stores info about avg sum. of sold cars in previous 7 days and 14 days from current date in row. It can looks like below:

date	no. Sold cars	7 days back from current day	14 days back from current day
2011-04-01	2	2	2
2011-04-03	4	6	6
2011-04-06	1	7	7
2011-04-07	5	12	12
2011-04-08	4	14	16
2011-04-10	3	13	19
2011-04-12	2	15	21
2011-04-13	4	18	25
2011-04-15	5	14	28

and on date 2011-04-15 for 14 days back sold 4+1+5+4+3+2+4+5 = 28 cars, and 2011-04-12 for 7 days back sold 1+5+4+3+2 = 15

to sum no of cars function below shoud look back deeper in previous row only if date in that row is not older than 7/14 days from date from current row

it has to be calulated while load script in code, not in layout layer (pivot, set analysis ...).

thanks for your tips.

Re: function below(sum(x),A,B) make all arguments calculated

Fri, 03 Jun 2011 18:16:52 GMT

Hi nowaitaka,

You could create a link between the table containing # of cars sold and a calendar date such as below:

CarsSold:
load * Inline
[
CalendarDate,no. Sold cars
2011-04-01,2
2011-04-03,4
2011-04-06,1
2011-06-07,5
2011-06-08,4
2011-06-13,3
2011-06-15,2
2011-06-16,4
2011-06-19,5
];
Dates:
load * inline
[
CalendarDate
2011-04-01
2011-04-02
2011-04-03
2011-04-04
2011-04-05
...
...
...
2011-06-26
2011-06-27
2011-06-28
2011-06-29
2011-06-30
];

Create a straight table and use the following dimensions:

Calendar date

Use the following Expressions to get sum of sales starting N days before the date in the row through the date in the row:

sum([no. Sold cars])
RangeSum(above(sum([no. Sold cars]), 0, 7))
RangeSum(above(sum([no. Sold cars]), 0, 14))

You could swap above with below to get the sum of sales starting the date in the row and ending after N calendar days.You probably don't even need sum function in the last 2 expressions.

John has answered a similar question about everyday sum of past 5 days.

Message was edited by: QueueWeRavn

function below(sum(x),A,B) make all arguments calculated

Tue, 07 Jun 2011 13:19:32 GMT

hi QueueWeRavn,

thx for your tip, this solution works partially.

If U do not make any selections in app that affects the dates, works fine - it takes previous 7/14 rows from stright table where we have days day by day which gives us 1 week and 2 weeks back...

but if U make selection in app that results in excuding some dates (like shops in City1 in last week sold cars only in monday and friday and not in each day) the stright table will show less dates but it`ll calculate expression "RangeSum(above(sum([no. Sold cars]), 0, 7))" from last 7 rows in this table (but previous 7 rows contains dates that are older than 7 days).

That`s why I was thinking about calculating this expression in data load script to avoid such situation...

My bad is that I didn`t mentioned about this selecions before ....

here is example output from this stright table...

exp1 =RangeSum(above(sum(D_EFECTIVELY_FREE_FIRST_SHORTER_PERIOD), 0, 7))

exp2 =sum(D_EFECTIVELY_FREE_FIRST_SHORTER_PERIOD)

date exp1 exp2

2011-06-01	2	2
2011-06-03	16	14
2011-06-05	62	46
2011-06-08	151	89
2011-06-10	206	55
2011-06-12	251	45
2011-06-15	514	265
2011-06-17	707	207
2011-06-19	752	91
2011-06-22	853	190
2011-06-24	828	30
2011-06-26	840	57
2011-06-29	845	270

if U tak any date it sums previous 7 rows even if this previous date is older than 7 days (it does not interprets the date - only takes 7 rows back... )

cheers

function below(sum(x),A,B) make all arguments calculated

Tue, 07 Jun 2011 17:35:44 GMT

Hi nowaitaka,

You have a very good point. It would be best to perform such calculations through SQL or other means during loading instead of performing calculations on the fly.

Re: function below(sum(x),A,B) make all arguments calculated

johnw — Tue, 07 Jun 2011 23:50:39 GMT

I'd suggest the solution in the link that QueueWeRavn posted. It doesn't require functions like above(), and it doesn't require you to precalculate the values. It just requires that you build a data model that "understands" what it means to be within the past 7 days or 14 days. There are more efficient ways to build this data (while loops), but this will probably be fine.

[AsOf]:
LOAD date(fieldvalue('Date',recno())) as "Date"
AUTOGENERATE fieldvaluecount('Date')
;
OUTER JOIN ([AsOf])
LOAD "Date" as "As of Date"
RESIDENT [AsOf]
;
OUTER JOIN ([AsOf])
LOAD * INLINE [
Date Type
Current Day
Rolling 7 Days
Rolling 14 Days
];
INNER JOIN ([AsOf])
LOAD *
RESIDENT [AsOf]
WHERE (   "Date Type"='Current Day'
      AND "Date"="As of Date")
OR (   "Date Type"='Rolling 7 Days'
      AND "Date">="As of Date"-7
      AND "Date"<="As of Date")
OR (   "Date Type"='Rolling 14 Days'
      AND "Date">="As of Date"-14
      AND "Date"<="As of Date")
;

Then build a pivot table like this:

Dimension 1 = "As of Date"
Dimension 2 = "Date Type" // move to top
Expression = sum([no. Sold cars])

All of the intelligence is in the data model, which is why the chart itself can be so simple.

Hmmm, let me tack a crack at the while loops approach.

[Dates]:
LOAD date(fieldvalue('Date',recno())) as "Date"
AUTOGENERATE fieldvaluecount('Date')
;
[AsOf]:
LOAD
"Date" as "As of Date"
,'Current Day' as "Date Type"
,"Date"
RESIDENT [Dates]
;
CONCATENATE ([AsOf])
LOAD
"Date" as "As of Date"
,'Rolling 7 Days' as "Date Type"
,date("Date"+1-iterno()) as "Date"
RESIDENT [Dates]
WHILE iterno() <= 7
;
CONCATENATE ([AsOf])
LOAD
"Date" as "As of Date"
,'Rolling 14 Days' as "Date Type"
,date("Date"+1-iterno()) as "Date"
RESIDENT [Dates]
WHILE iterno() <= 14
;
INNER JOIN ([AsOf])
LOAD "Date"
RESIDENT [Dates]
;
DROP TABLE [Dates]
;

function below(sum(x),A,B) make all arguments calculated

Fri, 10 Jun 2011 10:38:10 GMT

Hello,

WHILE statement with iterno() , RecNo() and peek() did the trick in my case . I thought native functions below/above will be the best solution but looks like while statement in LOAD is also optimized in QV so it works good enought for me.

Looks like i have to do some research about good practise while develop model in QV.

Thanks to you both for help, cheers.

Here is my solution from real code (car sale model was only example).

Mytab:

LOAD

D_DEPARTURE_DATE as D_DEPARTURE_DATE_14 ,

D_DESTINATION as D_DESTINATION_14,

peek('D_EFECTIVELY_FREE_SEATS_14',RecNo()-IterNo(),'DEPARTURESv5') as D_TO_SUM_14

resident DEPARTURESv5

while

(RecNo()-IterNo())>-1 and date(date(D_DEPARTURE_DATE)-13) <= date(peek('D_DEPARTURE_DATE',RecNo()-IterNo(),'DEPARTURESv5')) and D_DESTINATION = peek('D_DESTINATION',RecNo()-IterNo(),'DEPARTURESv5');

//RowNo() 1->

//IterNo() 1->

//peek 0->

and then group by and sum(D_TO_SUM_14)

function below(sum(x),A,B) make all arguments calculated

Fri, 10 Jun 2011 13:01:51 GMT

Hi nowaitaka,

Great! I learnt something new from your implementation as well. Thank you for sharing.

Re: function below(sum(x),A,B) make all arguments calculated

Tue, 17 Jan 2012 22:25:00 GMT

Awesome! this really helped me.