Creating a expression from multiple columns in Chart

[nadkalyan]

Hello,

I am designing a funnel chart to show various stages and here is how my data in database table looks like-

Customer	Stage0Count	Stage10Count	Stage20Count	Stage50Count	Stage70Count	Stage100Count
00001	1	0	1	0	1	0
00002	0	1	1	1	1	1

I put my Stage(Data is like 0%, 10%, 20%, 50%, 70%, 100%)  as Dimention and I tried to put in a IF condition  and Set expression  and  in the Expression(called it as StageCount) of the chart, something like

= sum( {< Stage ={'10%','20%','50%','70%'} >}

           IF(Stage10Count<>0, Stage10Count,

          IF(Stage20Count<>0, Stage20Count,

          IF(Stage50Count<>0, Stage50Count,

          IF(Stage70Count<>0, Stage70Count)))))

The intent was to show only 0,10,20,50,70 stage counts in the chart.

However when i look at data in the chart i see some stages are skipped - here is how it looks -

Customer	Stage	StageCount
00002	50%	1
00002	70%	1

         As you can see it skipped stages 10, 20 even though their count is 1.

Is this the right way to design to create a single expression from multiple columns. Is there any tweeks that i need to be doing.

Can someone please help me if I am missing anything.

Thanks

Kalyan

[swuehl]

How is your Stage dimension linked to your above data model table?

Is it a data island?

You could transform your table from a crosstable to a straight table in your model using the CROSSTABLE LOAD prefix, something like

CROSSTABLE (Stage, Count) LOAD * FROM YourTable;

You then need to transform your Stage values derived from that so it does match your Stage dimension field, thus linking your two tables / fields.

Besides that, something like

IF(Stage10Count<>0, Stage10Count,

          IF(Stage20Count<>0, Stage20Count,

          IF(Stage50Count<>0, Stage50Count,

          IF(Stage70Count<>0, Stage70Count))))

Is just returning one stage count at most, whatever first count different from zero. If you want to sum different fields, try rangesum:

rangesum(Stage10Count, Stage20Count, Stage50Count, Stage70Count)

[nadkalyan]

Hello swuehl,

First of all, Thanks for very quick reply.

The first table in my original post is stagehistory table, that captures history of a customer over various stages. The actual stage dimension sits in Stage master table and it is joined to this table by customer number key. The master stage table shows only current stage, the customer is at......for Example in above table Customer 00002 is currently at 100% stage while Customer 00001 is at 70% stage.

I am not sure about Crosstable logic as how can I do CROSSTABLE (Stage, Count)  Load * from Table; when i don't have Stage in my table.

The If statement is just returining random 2 stage counts data as i showed in my second table.It is showing only 50%, 70% but skipping 10%, 20%.

Can i do rangesum using set analysis like RangeSum( {<Set analysis>} Stage10Count, Stage20Count, Stage50Count, Stage70Count) ???

Please let me know if this makes sense?

Again appreciate for all your help

Thanks

Kalyan