Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Not applicable
Author

Anyone?

robert_mika
Master III
Master III

Attached your data or qvw file

Not applicable
Author

Here's the qvw.

The Expression that im talking about is SOH DEN

Thanks Robert

kuba_michalik
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

Don't understand it at all.

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

kuba_michalik
Partner - Specialist
Partner - Specialist

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]> .

Not applicable
Author

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.

kuba_michalik
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

Thanks.

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