Divide a factor per day in script

supriyabiware — Tue, 30 Jan 2018 09:08:00 GMT

Hi,

i have a requirement to divide a factor called 'QUOTE' on per day basis!. i have written the below code but seems it doesnt work.

/**********************************************************************************************

**********************************************************************************************/

SET vMinYear=2014;

SET vMaxYear=2018;

LET vMinDate=num(MakeDate($(vMinYear), 1, 1));

LET vMaxDate=num(MakeDate($(vMaxYear), 1, 1));

//All Days of the given period

Quota_Days:

LOAD $(vMinDate) + rowno() -1 AS QuotaDay

AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;

Quota:

LOAD

%key1,

QUOTE AS Quota,

if(peek(%key1) = %key1, peek(EINSATZDATUM)-1, $(vMaxDate)) AS validTo,

num(date(EINSATZDATUM)) AS validFrom

RESIDENT MAIN_TABLE

ORDER BY %key1, EINSATZDATUM DESC;

//create Intervals to Dates

Quota_Intervals:

INTERVALMATCH (QuotaDay) LOAD DISTINCT validFrom, validTo

RESIDENT Quota;

DROP TABLE Quota_Days;

//join Dates into interval

LEFT JOIN (Quota)

LOAD

validFrom,

validTo,

QuotaDay,

Year(QuotaDay) AS QuotaYear

RESIDENT Quota_Intervals;

DROP TABLE Quota_Intervals;

//Make the yearly avg

LEFT JOIN (MAIN_TABLE)

//QUOTE:

LOAD

%key1,

QuotaDay,

QuotaYear ,

avg(Quota) AS Quota

RESIDENT Quota

GROUP BY

%key1,

QuotaDay;

QuotaYear;

DROP TABLE Quota;

Can someone help me correct this one? i get output just AVG of QUOTE and not divided by per day in that year of EINSATZDATUM (start date)

Thanks

Supriya

Re: Divide a factor per day in script

mohan_1105 — Tue, 30 Jan 2018 09:21:25 GMT

Try using,

avg(Quota) / Count(QuotaDay) AS Quota

Re: Divide a factor per day in script

supriyabiware — Wed, 31 Jan 2018 12:12:41 GMT

Thanks , but somehow, i am not getting all dates. Quota day is just one date

Re: Divide a factor per day in script

mohan_1105 — Wed, 31 Jan 2018 12:19:18 GMT

If my understanding is correct then you can load the script with,

avg(Quota) / ($(vMaxDate) - $(MinDate)) AS Quota

topic Divide a factor per day in script in QlikView

Divide a factor per day in script

Re: Divide a factor per day in script

Re: Divide a factor per day in script

Re: Divide a factor per day in script