Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

juhoheino
New Contributor III

Alternative to Set Analysis in Load Script

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

1 Solution

Accepted Solutions

Re: Alternative to Set Analysis in Load Script

Funnel:

LOAD

    Date(floor(ReloadTime()), 'D.M.YYYY') as DateofReload,

    Sum(EstimatedValue*(CloseProbability/100)) as ValueofFunnel,   

    Sum(if(StatusCode = 200000, EstimatedValue*(CloseProbability/100))) as ValueofOffers,

    Sum(if(StatusCode = 1, EstimatedValue*(CloseProbability/100))) as ValueofProspects,

    Sum(if(StatusCode = 200008, EstimatedValue*(CloseProbability/100))) as ValueofWPCs,  

    Sum(if(StatusCode = 2, EstimatedValue*(CloseProbability/100))) as ValueofOnHolds

Resident Fact         

Where StateCode = 0;


talk is cheap, supply exceeds demand
6 Replies

Re: Alternative to Set Analysis in Load Script

Your load statement is missing a Group By clause for StatusCode. So if you add Group By StatusCode below the Where clause it should work.


talk is cheap, supply exceeds demand
juhoheino
New Contributor III

Re: Alternative to Set Analysis in Load Script

Ok, what is the correct syntax to do this? If I add it like below, the where-keyword turns to red (same color as StatusCode) and the script says 'field not found - <where>.

Resident Fact       

Group By StatusCode,

Where StateCode = 0;

Re: Alternative to Set Analysis in Load Script

Try:

load

    ...etc

Resident Fact        

Where StateCode = 0 

group by StatusCode;


talk is cheap, supply exceeds demand
juhoheino
New Contributor III

Re: Alternative to Set Analysis in Load Script

Stupid of me not to try it that way...

whatitdoesnow.PNG

This is the result it currently saves. What do I need to do to get data of a single reload date to a single row (with date as key)?

2.PNG

Re: Alternative to Set Analysis in Load Script

Funnel:

LOAD

    Date(floor(ReloadTime()), 'D.M.YYYY') as DateofReload,

    Sum(EstimatedValue*(CloseProbability/100)) as ValueofFunnel,   

    Sum(if(StatusCode = 200000, EstimatedValue*(CloseProbability/100))) as ValueofOffers,

    Sum(if(StatusCode = 1, EstimatedValue*(CloseProbability/100))) as ValueofProspects,

    Sum(if(StatusCode = 200008, EstimatedValue*(CloseProbability/100))) as ValueofWPCs,  

    Sum(if(StatusCode = 2, EstimatedValue*(CloseProbability/100))) as ValueofOnHolds

Resident Fact         

Where StateCode = 0;


talk is cheap, supply exceeds demand
juhoheino
New Contributor III

Re: Alternative to Set Analysis in Load Script

Many thanks!

Community Browser