How to perform aggr on the dimensions which are not part of the chart

[Anonymous]

Hi ,

      We have following data. and trying to display in a straight table

      Dimension: age_group

      Expression: sales for eache age_group but need to exclude the member sales who crosses 350.

      The expected straight table is :

      Age_group    sales (excluded 350)

      85-95             175

      65-75             176

as 103,100 and 104 crosses 350 sale.

    I tried to use following expression but it is not working when we dont have mbr_id in the stright table.

     sum(sales)-max(aggr(sum(sales),mbr_id))

Data table:

mbr_id	age_group	sales
100	85-95	100
100	85-95	200
100	85-95	50
101	85-95	100
101	85-95	25
101	85-95	50
102	65-75	100
102	65-75	26
102	65-75	50
103	65-75	100
103	65-75	200
103	65-75	100
104	85-95	361

Thanks

[JonnyPoole]

If the total sales for the mbr_id (regardless of age group) is <= 350, then include the member sales and show me the sales by age groups just for those member ids:

sum( if(  aggr( sum ( sales) , mbr_id) <= 350, aggr( sum ( sales) , mbr_id)))

[jyothish8807]

Hi Dasu,

Try this:

Dimension1:

=if(aggr(sum(sales),mbr_id)<350,mbr_id)

Dimension2:

age_group

Exp1:

Sum(sales)

Note: For Dimension 1 select supress for null value.

Regards

KC

[Anonymous]

Hi,

      There is an issue here. 85-95 group should have only 175 but in the application you have provided got value 525 that means it is not doing the condition on member level.

[JonnyPoole]

It is correct (i think) because for this age group member 100  is 350 and  and member 101 is 175 (350+175 = 525)

The filter is <= 350.

If it was < 350  then you would be correct.

Which one do you want ?

[Anonymous]

This is good if i have just one fact value that is sales. In case if i have another fact called asset and there should not be any data exclusion then how will we proceed?. See the screen shot

[jyothish8807]

Hi Dasu,

What do you want to show in asset field?

Regards

KC

[jyothish8807]

Hi Dasu,

Is this is what you want?

Regards

KC