Tabela dinâmica - como evitar que a tabela possa ser expandida / pivot table how prevent expand lines of total

[Anonymous]

In a PivotTable, I have some lines total, how can I prevent the user can specifically expand these lines?

Can anyone help me?

/

Em uma tabela dinâmica, eu tenho algumas linhas de totais, como posso eu evitar que o usuário possa expandir especificamente essas linhas ? Alguém pode me ajudar ?

Thanks

Evandro

[Clever_Anjos]

Apenas algumas linhas em particular ou todas?

Se for apenas algumas, não é possível

[#3]

Hi Evandro

You mean you have totals lines in the data itself? (because you can't expand the "partial sums" totals that QlikView calculates itself)

As Clever Anjos mentioned, you can't specify which lines can be expanded and which can't. However you can workaround, either

1) Use the "partial sums" option mentioned above- this is in the presentation table in the chart properties

2) In your 2nd and third dimensions, you could add a conditional dimension that returns the normal row values when the 1st dimension is not a total, and returns blank (or some text) if it is a total.

EG: Dimension #1 : =[Store Name]

Dimension #2 : = if([Store Name] = 'Total', 'Total','[Salesperson])

This would aggregate all the salespeople that would otherwise be listed under the first dimension total. So although it "allows" expansion, what it would return would just be one line, "total"

Oi Evandro, vou tentar responder em portugues!

Parece o que, em suas dados tenho os linhas de totais, verdade?

Sera, nao da para evitar expandir os linhos , como diz a clever anjos.

Mais, o qlikview tem opcao para fazer os totais no chart object - o opcao e no presentation tab.

Alternativamente, pode fazer um dimensao com condicaos que retorne o palavra "total" quando o primero dimensao e um total e os dados normais quando nao e.

Isso aggregara os linhas no segundo dimensao, e quando o usario tenta expandir, so expande a mais uma linha.

Fala se voce nao entendiu!

Erica

[Anonymous · Accepted solution]

Hi Erica,

Thanks for the feedback.

My database is only analytical , do not keep totals.

I thought of the following solution and I succeeded .

As an example use case, we have a pivot table with 2 columns and 2 dimensions of expression (values ​​in January and February ) .

The idea is to show the line of net revenue (gross revenue - deductions) , highlighted in blue , as a calculation line , blocking her drill -down through a flag of the group (line) parameter.

Requirements / Steps :

Imagine the fully contracted PivotTable . Let's call the PivotTable from now simply " chart " , and each of its lines of 'group' .

1 ) For each row of the FACT table you must store the corresponding group presentation on the chart. In the example below , Left ( GROUP,1) indicates the group or presentation line on the graph :

2 ) In your data source , you need to have the display parameters of each group, such as:

a)  Relationship between FACT and GROUPS = LEFT ( FACT .GROUP, 1) with GROUPS.GroupCode

b ) [ GroupDescription ] is the first dimension of the graph

c ) { GroupSignal ] determine whether the value is displayed with positive or negative sign

d ) [ GroupOrder] will be sort of group

e) [ GroupFlag ] here's the secret : this is responsible for expanding or not the drill-down in the group ( 0 = does not expand / 1 =user can expand the node)

f ) [ GroupColor ] merely aesthetic

3 ) In the spreadsheet data source , save the expressions in variables and for each month ( or expression of the graphic ) , use two variables , where the first determines whether the node can be expanded ( eg vCalcJAN ) that use Dimensionality() function combined with [GroupFlag] attribute, thus directing the resolution second expression that effectively perform the calculation ( vExprCalcJAN ) :

4 ) In EXTRACTOR application , load DIMENSION [ GROUP ] from the load of the CONTAS and groups from the table [ FACT ] concatenating with table [GROUPS] for the display parameters . Use "distinct " in the " LOAD " command.

5 ) Load the FACT table , using the same composition as the key towards GROUPS table , ie , CONTA & Left ( group, 1 ) .

6 ) From this Cartesian product , resulting in what is expected of the graph , as illustrated below:

ATTACHED , you can find all sources of model created. First execute EXTRACT_DRE.qvw

Thank You!

Evandro Segura

[#5]

No problem Evandro, glad it worked!