Quarterly KPIs Calculation - Qlik Community

mario-sarkis · ‎2017-12-22

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 · ‎2017-12-22

May be this?

Avg(TOTAL <Date> Achievement)

OR

Above(Avg(Achievement),0,3)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

mario-sarkis · ‎2017-12-22

Hi Anil ,

is the expression for the Script?"

I need to calculate it is the script

Thank you

Frank_Hartmann · ‎2017-12-22

try this:

1:

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

2:

Load

CalendarQuarter,

Branch,

avg(Achievement) as avg

Resident 1

Group by CalendarQuarter,Branch;drop Table 1;

hope this helps