topic Re: TOTAL<> clause affecting set operators in QlikView

TOTAL<> clause affecting set operators

Tue, 20 Jan 2015 17:35:18 GMT

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

Re: TOTAL<> clause affecting set operators

Mon, 26 Jan 2015 15:13:15 GMT

Anyone?

Re: TOTAL<> clause affecting set operators

robert_mika — Mon, 26 Jan 2015 15:15:23 GMT

Attached your data or qvw file

Re: TOTAL<> clause affecting set operators

Mon, 26 Jan 2015 16:18:18 GMT

Here's the qvw.

The Expression that im talking about is SOH DEN

Thanks Robert

Re: TOTAL<> clause affecting set operators

kuba_michalik — Mon, 26 Jan 2015 16:30:29 GMT

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.

Re: TOTAL<> clause affecting set operators

Mon, 26 Jan 2015 17:09:23 GMT

Don't understand it at all.

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

Re: TOTAL<> clause affecting set operators

kuba_michalik — Tue, 27 Jan 2015 09:23:50 GMT

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

Re: TOTAL<> clause affecting set operators

Tue, 27 Jan 2015 09:35:47 GMT

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.

Re: TOTAL<> clause affecting set operators

kuba_michalik — Tue, 27 Jan 2015 09:57:24 GMT

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.

Re: TOTAL<> clause affecting set operators

Tue, 27 Jan 2015 10:00:46 GMT

Thanks.

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

Re: TOTAL<> clause affecting set operators

Tue, 27 Jan 2015 10:10:11 GMT

FOR EXAMPLE

FOR NIVEL3 = 01 - THe SOH NUM is : THE SUM OF RESPUESTA WHEN NIVEL 04 = 01 and NIVEL3 = 01 where IDSITIO IN ( NIVEL 4 = 00 and RESPUESTA = 'SI' and NIVEL3 = 01)

FOR NIVEL3 = 01 - THe SOH DEN is : THE SUM OF RESPUESTA WHEN NIVEL 04 = 01 where IDSITIO IN ( NIVEL 4 = 00 and RESPUESTA = 'SI' and NIVEL3 = 01)

The difference should be that in the DEN, the sum of the RESPUESTA when NIVEL 04 = 01 is not based on the NIVEL3, should be all the sum independing on NIVEL3, but the restriction of the IDSITIO List SHOULD be restricted by each row.

Hope its clear