topic Different from Total Count table, and Total count for row in QlikView

Different from Total Count table, and Total count for row

danosoft — Mon, 12 Aug 2019 13:13:21 GMT

Hi, i have this problem my Total in my chart table on the top, is different from the total amouts in my rows... how can is possible?

if i put it on excel and i count the column it say me 626, but qlikview say me 625.

i attach the file, my set analisys in this colum is:

Count(DISTINCT {<DATA_IN={"<=$(=Date($(vFineMonthContrMaxMonth), 'DD/MM/YYYY'))"},DATA_OUT={">$(=Date($(vFineMonthContrMaxMonth), 'DD/MM/YYYY'))"}>} COD_CLIENTE)

where the variable $(vFineMonthContrMaxMonth) is 31/07/2019

what is wrong?

Re: Different from Total Count table, and Total count for row

Gysbert_Wassenaar — Mon, 12 Aug 2019 13:30:55 GMT

Nothing is wrong. You count distinct customers. It seems you have customers that are counted in the numbers of more than one detail row of your table. They're unique per row, but not unique over all rows so summing the totals of the rows gives you a larger number than the unique number of customers overall.

If you want to sum the distinct counts of the rows then you should either do another level of aggregation or set the total mode of the chart expression to sum instead automatic.

The sum of counts can be calculated with something like sum(aggr( count(....etc) , dimA, DimB, ...etc )). Where dimA, DimB, ...etc are the names of the fields you used a dimensions in your chart.

Re: Different from Total Count table, and Total count for row

danosoft — Mon, 12 Aug 2019 13:41:10 GMT

Sorry but i not understand what you mean,can you do the example with my set analisys i sent, for count well?

Thanks

Re: Different from Total Count table, and Total count for row

Gysbert_Wassenaar — Mon, 12 Aug 2019 13:42:55 GMT

Sum(aggr(Count(DISTINCT {<DATA_IN={"<=$(=Date($(vFineMonthContrMaxMonth), 'DD/MM/YYYY'))"},DATA_OUT={">$(=Date($(vFineMonthContrMaxMonth), 'DD/MM/YYYY'))"}>} COD_CLIENTE),dimA,DimB,...etc))

Re: Different from Total Count table, and Total count for row

danosoft — Mon, 12 Aug 2019 13:48:00 GMT

Thanks, but my dimension are not always the same, i use a Group cycle.

Before you wrote this too: "or set the total mode of the chart expression to sum instead automatic" how?

Re: Different from Total Count table, and Total count for row

Gysbert_Wassenaar — Mon, 12 Aug 2019 13:51:37 GMT

Open the Expressions tab of the properties window of the chart. Select your expression in the list and change the Total Mode from Expression Total to Sum (of Rows)

Re: Different from Total Count table, and Total count for row

danosoft — Mon, 12 Aug 2019 13:55:11 GMT

And this it will do the aggregation in automatic?

Otherwise if i want to do with your set analisys you wrote, what i need to put in Dim1, Dim2... if i use ony a Dimension but it is a Cycle Group, so it can change ?

thanks

Re: Different from Total Count table, and Total count for row

danosoft — Mon, 12 Aug 2019 14:28:56 GMT

Hi, sorry but i think is a problem in your solution, becouse look at this attachment: you see i have 2 different value in my 2 tables.

In the first i have 3.381.185 this is a chart table with this expression:

Sum(DISTINCT {<DATA_IN={"=Aggr(Max(DATA_IN), MESEIN, COD_PUNTO)"}>}if(Num(Interval(DATA_OUT-DATA_IN,'DD'))>2, CONSUMO_TOT_ANNUO))

and setting the total amount in the chart like you said. The dimensions are three

In the second tabe i have the same expression with the same total amout setting, but i obtain a different value: 3.357.609

in this second table i have a cycle group for dimensions

If i use the set analisys like you said, for example in this case in my second table :

Sum(aggr(Sum(DISTINCT {<DATA_IN={"=Aggr(Max(DATA_IN), MESEIN, COD_PUNTO)"}>}if(Num(Interval(DATA_OUT-DATA_IN,'DD'))>2, CONSUMO_TOT_ANNUO)),AGENTE)) i obtain another result, and if i put for dimension the other in the cycle group... i obtain another result......

So... what is the right way?

I need only to Sum a distinct of values.....

Re: Different from Total Count table, and Total count for row

Brett_Bleess — Wed, 28 Aug 2019 18:12:15 GMT

Daniele, what is most helpful in these situations is for you to attach the QVW file, as this lets folks see the data model as well as all the objects and expressions, so they can figure out exactly what you have done in relation to what you are trying to do. Best recommendation I can give you if you are still looking for help. If you have fields that you are worried about folks seeing values, use the Settings\Document Properties\Scrambling tab to scramble those field values of concern before attaching to the post. A sample app with sample data showing things also works.

Regards,
Brett