The business need is to get 'point in time' reporting regarding CRM's funnel (on a daily/weekly/monthly etc level). As the MS CRM database does not keep track of the historical value of the funnel, I have to save it to a separate qvd during my backend reload to get this historical data.
I have already managed to save the amount of the whole funnel when dimension StateCode = 0 (which means open opportunities). Our Sales Manager, however, wants to view the funnel value as a stacked bar chart with different StatusCodes making the stacks in the chart (offers, prospects, won pending contracts & on holds).
So the to be data collected per reload should be DateofReload, value of offers, value of prospects, value of on-holds and value of won pending contracts with these last four summing up to the value of the whole funnel.
The code below will not reload with QlikView saying that the expression is invalid. What is wrong with the expression? Or is there a better approach to tackling this specific problem?
Date(floor(ReloadTime()), 'D.M.YYYY') as DateofReload,
Sum(EstimatedValue*(CloseProbability/100)) as ValueofFunnel, // whole value of funnel
if (StatusCode = 200000, Sum(EstimatedValue*(CloseProbability/100))) as ValueofOffers,
if (StatusCode = 1, Sum(EstimatedValue*(CloseProbability/100))) as ValueofProspects,
if (StatusCode = 200008, Sum(EstimatedValue*(CloseProbability/100))) as ValueofWPCs, // WPCs = won pending contracts
if (StatusCode = 2, Sum(EstimatedValue*(CloseProbability/100))) as ValueofOnHolds
Resident Fact // EstimatedValue etc. are loaded from Fact (data from Opportunity-table in CRM)