Managing which records are visible in detail table (Extn of question in discussion 312668)

[vvira1316]

Hi,

I needed the KPI#, Detail table and Bar Chart to show/keep same count / data for a partcular dimensional value when selection was made or not. With Ruben's ( rubenmarin ) help I got that working.

Need to Keep Count and Data Same what is seen for a particular category when category selection is d...

The only challenge I'm facing is in detail table when selection is made. I'm seeing records that should not be visible. I would like to control them either through a measure expression, or changing dimension calculation. I've included the sample qvf file for your reference.

I'll explain the requirement through screen shots as well.

Same issue in second table that belongs to second bar chart and second formula.

I've two variation of sum formula.

expression 1

=Sum({<BSN={"*"}>} Aggr(If(Max({1} TOTAL <DIM_FLD1_Id> [Data Date]) = [Data Date], 1, 0), DIM_FLD1_Id, OFFNM, BSN, [Data Date]))

expression 2

=Sum(Aggr(If(

Max({1} TOTAL <DIM_FLD1_Id> [Data Date]) <= Today()- 30
and Max({1} TOTAL <DIM_FLD1_Id> [Data Date]) > Today() - 60
and

(
(FirstSortedValue(OFFNM, -[Data Date]) = 'OFFNM1' and FirstSortedValue(OSN, -[Data Date]) >= 3)
or (FirstSortedValue(OFFNM, -[Data Date]) = 'OFFNM2' and FirstSortedValue(OSN, -[Data Date]) >= 5)
or (FirstSortedValue(OSN, -[Data Date]) = 😎
or (FirstSortedValue(BSN, -[Data Date]) >= 2)
)

and Max({1} TOTAL <DIM_FLD1_Id> [Data Date]) = [Data Date], 1, 0), DIM_FLD1_Id, [Data Date]))

The count formula from earlier discussions

How to exclude certain records from appearing in the straight table

Set analysis to get expected output

=Count(DISTINCT {<DIM_FLD1_Id = {"=Max([Data Date])"}>} DIM_FLD1_Id)

Thanking you in advance.

BR,

Vijay

[sunny_talwar · Accepted solution]

I think you need to change your rest of the expressions to make sure that they don't show a value when count is 0. I have used this for example for OFFNM

=If(Column(1) = 1, FirstSortedValue(OFFNM, -[Data Date]))

and done the same for others... check it out now