Divide a factor per day in script

[supriyabiware]

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

[mohan_1105]

Try using,

avg(Quota) / Count(QuotaDay) AS Quota

[supriyabiware]

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

[mohan_1105]

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

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