Field filtering on pivot table chart

[#1]

Hi comunity!

I´m trying to do a threasury report using pivot table chart. First of all, I designed a complete report with banks, customers and suppliers info ordered by date to use it daily. But my problem comes when I want to create the same report but with its info limited on BANKS value from a specific field, I mean:

If I select dimensions tab on chart properties and I edit this field I want to filter only BANKS value (not CUSTOMERS and SUPPLIERS) I don´t know which formula I have to use. I trying to do this instead of using bookmarks (other way) to explore other qlikview´s possibilities and becasuse is a very very specific report. A second question is if I filter in that way, I only have to use SUM(IMPORT) expresion, haven´t I? (it´s not necessary to use if function)

Any suggestions?

Thanks you, Fiber.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

It depends largely on how your document is built. If you have different fact tables for customers, suppliers and banks, so Bank can be used as dimension, then is as you say, Bank as dimension, and Sum(IMPORT) as expression.

If not, I'd use set analysis

Sum({< SourceType = {'Banks'} >} IMPORT)

SourceType here is the name I use to distinguish among the different facts I have (say one large table with all suppliers, customers and banks).

Hope that helps.

[#3]

Hi MIguel A.!

First of all, excuse me not to reply as quikly as you answer me, I couldn´t.

I tried your option and it works, but pivot table is showing the rest of values like customers and suplliers with sum(import)=0. That´s not what I'm looking for; I just want to see 'banks' value in my only and main dimension with its sum(import) when every field in QV are clear. It´s like a fix report.

It´s possible? I suppose.

Thanks again, Fiber

[Miguel_Angel_Baeyens]

Hi,

I'd use then either

If(Sum({< SourceType = {'Banks'} >} IMPORT) > 0, Sum({< SourceType = {'Banks'} >} IMPORT))

as expression see how this performs.

Or the following as calculated dimension

=If(Aggr(Sum({< SourceType = {'Banks'} >} IMPORT), Dimension) > 0, Dimension, null())

And check for this dimension suppress null values.

Hope that helps

[#5]

Miguel, excuse me for to reply before. I didn´t prove it since today. It works fine.

Thank you, Fiber