Hello guys!
I have KPI's and actual/target values by quarters for each sales representative & the % of target achievement which is calculated monthly and quarterly when the quarter is over.Excel file with the format of data is attached .Problem is that each KPI is calculated differently.For example, average of balance and deposit should show average of data for KPI for the months available (For Q1 if we have only jan and feb, calculate their average and divide by target) and for others like Salary card, Loan outstanding I divide value for last month available to the ttarget value.For total revenue and non-interest revenue, I sum available months and so on. So the problem is that when I use Multiple KPI visualization my average balance and deposit value sums up data for Q1 and divides it by the sum of months(months are indicated as 1,2,3, so it divides by 6). Also, I cannot anyhow divide non-interest revenue to total revenue to show % of non-int revenue which is also needed. After completing this stage I also need to multiply target achievement % of each SR to the weights to get overall performance, but I am already stuck and don't know what to do next.
Hoping somebody can help me with the script. I am attaching scripts, unfortunately, I can't attach qvd because of the security policy of my company.
QueryScript is table in DWH with actual data,target data comes from excel.
