Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
supriyabiware
Contributor

Divide a factor per day in script

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

3 Replies
Partner
Partner

Re: Divide a factor per day in script

Try using,

avg(Quota) / Count(QuotaDay) AS Quota

supriyabiware
Contributor

Re: Divide a factor per day in script

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

Partner
Partner

Re: Divide a factor per day in script

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

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