Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

TOTAL<> clause affecting set operators

Hi,


This is my Scenario.

I've a Pivot Table Graph with 4 dimensions DESC_PRODUCTO, DESC_SUBPRODUCTO,DESC_EMPRESA and DESC_MARCA

This is my Expression :

sum({$<M.ID_SITIO = p({$<FlagCY={1},M.FECHA={">=$(#desde)<=$(#hasta)"},M.NIVEL4 ={'00'}, M.RESPUESTA = {'Si'}> }M.ID_SITIO),M.NIVEl4 ={'01'},FlagCY={1},M.FECHA={">=$(#desde)<=$(#hasta)"}>} total<DESC_SUBPRODUCTO> num(M.RESPUESTA)),'-')

The problem is that the Total<desc_subproducto> it should affect only the sum(M.RESPUESTA) and not also the p({$<FlagCY={1},M.FECHA={">=$(#desde)<=$(#hasta)"},M.NIVEL4 ={'00'}, M.RESPUESTA = {'Si'}> }M.ID_SITIO). So i want that the list of M.IDSITIO is based on the dimension (DESC_MARCA) but the total ruin it.

Any idea?

Thanks

Albert

10 Replies
Highlighted
Not applicable

Re: TOTAL<> clause affecting set operators

Anyone?

Highlighted

Re: TOTAL<> clause affecting set operators

Attached your data or qvw file

Highlighted
Not applicable

Re: TOTAL<> clause affecting set operators

Here's the qvw.

The Expression that im talking about is SOH DEN

Thanks Robert

Highlighted
Partner
Partner

Re: TOTAL<> clause affecting set operators

You don't have a problem with the total<> affecting the set expression. You have a problem with the fact that set analysis is always evaluated first, and chart dimensions second. There is no way to have dimension values for a table cell influence anything within the {}. Set expression (and the resulting set as well) will always be the same for a single expression for the entire table.

Highlighted
Not applicable

Re: TOTAL<> clause affecting set operators

Don't understand it at all.

The concatenation of ID's is calculated row by row, in the other Expression.

Highlighted
Partner
Partner

Re: TOTAL<> clause affecting set operators

Yes, that's because dimensions influence the concatenation, otherwise they would be pretty useless

However, the list of ID_SITIO that "p({$<[NIVEL 4] ={'00'}, RESPUESTA = {'Si'}> }ID_SITIO)" gives you is the same in every row. Dimensions don't touch it.

Anyway, that's a bit theoretical, as I'm not sure this set analysis for ID_SITIO does anything in the example. If I remove it everything stays the same. So, to get more to it, what is wrong with the values in SOH DEN, and what would you expect? If it' supposed to be sum of everything in one group with the same NIVEL 2 (the same as what would you get if you had subgroup totals displayed), then please try TOTAL<[NIVEL 1],[NIVEL 2]> .

Highlighted
Not applicable

Re: TOTAL<> clause affecting set operators

Thanks for the answer Jakub, but thats the main problem, that the list of ID_SITIO given by the p function is NOT the same in every row, so its definetly doing it right on the chart. The problem is when is followed by the Total Clause.

Ive tried before agregating the previous dimension to the total, but still gaves the same result

Thanks.

Highlighted
Partner
Partner

Re: TOTAL<> clause affecting set operators

OK, but could you say what should be under SOH DEN in the example, in numbers? It's hard to zero in on the correct result when you don't know what it is

And really, neither "Total" nor chart dimensions influence set analysis (including the p function), because it's not how it works. The list returned by p() is the same in every row, but some values from that list will go under one dimension value, some under the other (the Total clause can influence that part only). Just like with normal selections - Total clause won't suddenly change your selections, but can change how the selected data will be distributed among dimensions.

Highlighted
Not applicable

Re: TOTAL<> clause affecting set operators

Thanks.

SOH DEN should  bring 3800 for NIVEL3 = 01, 1745 for NIVEL3 = 05, 170 for NIVEL3 = 09,