topic Quarterly KPIs Calculation in QlikView

Quarterly KPIs Calculation

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

Hello Guys I have ,

I have a monthly Achievement Data as the below example(of caurse multiple branch is available in my data set)

Branch Date Achievement

002 01-2017 95

002 02-2017 96

002 03-2017 94

002 04-2017 90

Output:

Branch Date Achievement

002 Q1 95 (Average of the three months)

002 Q2 90 (since the available month in Q2 is April so the average Achievement is 90)

I Need to calculate the above in the script

Hope that you can help.

Thank you,

Anil_Babu_Samineni — Fri, 22 Dec 2017 08:52:36 GMT

May be this?

Avg(TOTAL <Date> Achievement)

Above(Avg(Achievement),0,3)

mario-sarkis — Fri, 22 Dec 2017 09:29:38 GMT

Hi Anil ,

is the expression for the Script?"

I need to calculate it is the script

Thank you

Frank_Hartmann — Fri, 22 Dec 2017 11:01:37 GMT

try this:

Load *,

'Q' & Ceil(Month(Date#(Date,'MM-YYYY'))/3) AS CalendarQuarter;

LOAD * INLINE [

Branch, Date, Achievement

002, 01-2017, 95

002, 02-2017, 96

002, 03-2017, 94

002, 04-2017, 90

];

NoConcatenate

Load

CalendarQuarter,

Branch,

avg(Achievement) as avg

Resident 1

Group by CalendarQuarter,Branch;drop Table 1;

hope this helps