Filter on Pivot Table expression

[#1]

Hello All,

I have a pivot table with 1 Dimension and 3 expressions.

Dimension: Journal ID

Expressions: Debit Amount(DR_Amt), Credit Amount(CR_Amt), P&L Impact (PLImpact)

I want to implement a functionality, where user can enter a 'threshold amount' in input box and Pivot table get updated to display records with Debit amount > threshold amount. Also, by default when application loads, table should display all records without any filtering.

I have tried following:

sum({<[Journal_ID]={'=sum(DR_Amt)>=$(vThreashhold)'}>}DR_Amt)

But in above case, it displays all records with '0' for DR_Amt <= vThreashhold, how do I filter out the records?

Also, is it possible to accept 2 variables and apply same condition on both Debit amount and P&L Impact amount (i.e. on 2 expressions of pivot table)

Thanks in Advance!

[#2]

Hi Aniket,

Check the attached application

[#3]

Vikram,

Thanks for the suggestion. But I have some questions. why are you applying threshold check on all 3 expressions?

Please find attached a slightly updated version, this is what I am trying to do. If you see in this version, I am checking only Sum(Debit) against threshold variable and I want to display all records with Sum(debit) > threshold. I have slightly modified original data to reflect what I have in my raw data.

Thanks,

Aniket

[#4]

Hi,

Q) why are you applying threshold check on all 3 expressions?

Ans: If i understand your requirement correctly, you need to view only those Journal Id's whose Debit amounts are greater than threshold values. By applying the check, i make sure that i get null values in CrAmount and Plimpact columns and then by suppressing zero values i get only those journal's whose debit amount is greater than threshold values

i have attached the modified version.