6 Replies Latest reply: Jul 8, 2013 2:40 AM by Heino Juho RSS

    Alternative to Set Analysis in Load Script

    Heino Juho

      Hi all!

       

      I read in http://community.qlik.com/thread/62774 that Set Analysis can not be used in Load Script, which is why I need an alternative.

       

      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?

       

       

      Funnel:
      LOAD
          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)
      Where StateCode = 0;    // open opportunities
      

       

       

      Many thanks,

       

      Juho