I was wondering if I could get some help with QlikView please.
The aim is to Identify what is causing the large variances in a specialists pipeline, whether it is a positive or a negative change or no change. I would also like to return the key details associated with that particular deal including the specialist name, client name, AUM, sales and provide an input box for the specialist to comment on the variance for the particular deal.
In excel, I used a long if statement with the criteria that could cause large variances in a column at the end of my large sales data set and then I would apply the filter and manually paste in the data for the sales people that have the largest variances into a separate table. I would like to automate this process in QlikView.
I have an idea, please see below but I am also welcome to suggestions on how I can display this info in Qlikview
Instead of a separate column, I would to incorporate the criteria into an expression and use conditional colors in the pivot table to reflect the criteria so I would see what is causing the variance based on the color. For example, Variance AUM = pink ect.
The criteria for the large variances is;
Variance CNR = CNR Difference >50 or <-100
Variance AUM - AUM difference > 25,0, <-25
Variance Probability - prior probability <50, current probability >49, or current CNR>50, Current AUM>25
Date - Projected close = Change, current AUM >25, current CNR>50
Variance CNR = CNR difference >50 or <-50
Variance AUM - AUM difference > 25, 0, <-25
Variance Probability - prior probability <50, current probability >49, or current CNR>75, Current AUM>25
Date - Projected close = Change, current AUM >25, current CNR>70
Please see attached for sample data set and result in excel.
Feel free to ask questions for more clarity, sorry for the long post.