I have a scenario that I have medical sales reps in a company, some of the reps call on pharmacies and some call on doctors.
I have a hierarchy setup so that if I select a manager in a listbox it filter on all that managers reps in the another lisbox. If I select a rep in the listbox I have another teamfield that tells me this is a pharmacy rep or a doctor rep.
My problem that I am struggling with is that according to the teamfield – Pharmacy or Doctor- I must change my dimension field in a pivot table.
If I select a rep and the rep is a pharmacy(teamfield) rep the dimension in the pivot must show and use the pharmacyname field and the expression would just be sum(sales).
If I select a rep that is a doctor(teamfield) rep the dimension field must change to another field called doctorname and the expression will be sum(sales).
The doctorname and pharmacyname are two separate fields.
I presume I can get the teamfield value in a variable but how do I use this to change my dimension field in pivot. I am using Qlikview 10
Help would be appreciated.