How to exclude certain records from appearing in the straight table

[vvira1316]

Hi,

I've an aging records table obtained using calculations from Set analysis to get expected output

I've included data files and applications file here as well.

Question1)

On the Aging worksheet I need to exclude records where OSN value is OSN8. They should not be part of detail or the count.

expected count 120, expected result file attached.

Question 2)

Count is not working when all values other than OSN8 are selected from filter pane for OSN.

Detail table should not show other records. (as seen in second image # 2)

Image # 1

Image # 2

[sunny_talwar]

On Latest Details tab?

[vvira1316]

yes

[sunny_talwar · Accepted solution]

Try this

=Sum(Aggr(If(Max(TOTAL <DIM_FLD1_Id> [Data Date]) = [Data Date], 1, 0), DIM_FLD1_Id, [Data Date]))

[vvira1316]

Thanks. It is looking good. Will update accordingly

[vvira1316]

Hi Sunny,

If I had to change the calculation for 30 Day aging information to be represented using above bar chart then what would be the formula?

Sum(Aggr(If(Max(TOTAL <DIM_FLD1_Id> [Data Date] <= Today() - 30 and FirstSortedValue(OSN, -[Data Date]) <> 'Complete'), 1, 0), DIM_FLD1_Id, [Data Date]))

BR,

Vijay

[sunny_talwar]

This

=Sum(Aggr(If(Max(TOTAL <DIM_FLD1_Id> [Data Date]) <= Today() - 30 and FirstSortedValue(OSN, -[Data Date]) <> 'OSN8' and Max(TOTAL <DIM_FLD1_Id> [Data Date]) = [Data Date], 1, 0), DIM_FLD1_Id, [Data Date]))

[vvira1316]

Perfect sir.... thanks.

[sunny_talwar]

Glad was able to help Vijay!!