Calculated Dimension correlated with expressions

[Anonymous]

Hi All

I have this pivot table where I'm segmenting customer based on 12 month revenue into A, B or C segment.

=if( Aggr( Sum({$<YEAR =, MONTH =>}NET), C_Number ) >= 60000, 'A',
if( Aggr( Sum({$<YEAR =, MONTH =>}NET),  C_Number) >= 25000, 'B',
if( Aggr( Sum({$<YEAR =>}NET),  C_Number) >= 0, 'C')))

I select the latest 12 month, and uses the below expression to count the # of customers in the first period.

Count(DISTINCT {$< DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}>}, C_Number))

This works perfectly.

However, I want to add expression which count # customers for the 12 month before the latest 12 months. Then i uses below expression:

Count(DISTINCT {$< DATE={'>=$(=MonthStart(Today(),-24))<$(=MonthStart(Today(),-12))'}>}, C_Number))

Which works perfectly as well. BUT the segmentation of the customers in the period from 12 month till 24 months, is based upon the revenue from the first periode, 0 months til 12 months.

Is there anyway to achieve a separately segmentation of the 2 periods in the same table?

Thanks

Regards

Stefan

[Anil_Babu_Samineni]

Like this you meant say?

Count(DISTINCT {$< DATE={"=(DATE >= $(=MonthStart(Today(),-12)) and DATE < $(=MonthStart(Today()))) and (DATE={"=DATE >= $(=MonthStart(Today(),-24)) and DATE < $(=MonthStart(Today(),-12))'})"}>} C_Number))

[Anonymous]

Hi Anil

This is not what I'm looking for.

There are two periods in my pivot table

Periode 1: 0 -12 months

Periode 2: 12-24 months

Segmentation of customers is based upon Revenue in one of those periods.

For example.

If customer X bought  $65.000 in period 1, then Customer X is classified as segment A.

However, in period 2 customer X only buys for $20.000, and is therefore classified as segment C.

So basically i want to achieve below

[Anil_Babu_Samineni]

You can create Calculated dimension like

Your simension is same for one column

=ValueList('Customer 0-12 mth','Customer 12-24 mth')

Then expression should like below

If(ValueList('Customer 0-12 mth','Customer 12-24 mth')='Customer 0-12 mth', Count(DISTINCT {$< DATE={'>=$(=MonthStart(Today(),-12))<$(=MonthStart(Today()))'}>} C_Number),

If(ValueList('Customer 0-12 mth','Customer 12-24 mth')='Customer 12-24 mth',

Count(DISTINCT {$< DATE={'>=$(=MonthStart(Today(),-24))<$(=MonthStart(Today(),-12))'}>} C_Number)))

[Anonymous]

How would that segment the customer into A, B or C?

[Anil_Babu_Samineni]

You have created thread with dimension of each segment only, You can use that for first dimension.

I would ask you to share QVW file to test

[sunny_talwar · Accepted solution]

I have created a sample using a very simple example... see if this can work for you

[Anonymous]

Hi Sunny

This works really well. Thanks a lot.

However, can seem to get the partial sums to work. Can you help here?

[Anonymous]

Hi Anil

Thanks for you're input. However, I'm not able to give a sample.

[Anil_Babu_Samineni]

This? Please enable the option Sum for expression if it is in straight table

If it is in pivot table, You can try like

Sum(Aggr(Expression, Your dim1))