topic Re: TCD : sum values when they are < 1 in QlikView

TCD : sum values when they are < 1

jleraille — Wed, 25 Nov 2020 16:16:04 GMT

In a TCD : How can 1 group the rows under a dimension 'Others' when Sum(Mnt) < 1
Dimension : Type
Expression : Sum(Mnt)

Type      Sum(Mnt)
Type1    20
Type2      5
Type3   0,4
Type4   0,9
Type5   1,2

Type      Sum(Mnt)
Type1    20
Type2      5
Type5   1,2
Others 1,3

Jacques

Re: TCD : sum values when they are < 1

ignacioroman — Wed, 29 Apr 2020 04:19:15 GMT

Sorry, I don't know what is TCD, but you want to do it in the script or in a chart?

If it is in the scrip, you could so something like that:

Load

if (sum(Mnt)<1,'Lower than 1', 'higer than 1') as [aux sum(mnt)]

from "table"

This is assuming "sum(Mnt)" is a variable whats comes from the table.

If you want to do it in the chart, you can follow this picture in the charts propieties (check the points with the arrows)

This is assuming "sum(Mnt)" is an expression.

Best regards!

Re: TCD : sum values when they are < 1

jleraille — Wed, 29 Apr 2020 10:29:10 GMT

Hello Ignacio roman

Thanks taking time to answerd.
Sorry TCD ("Tableau croisé dynamique" in french) is Pivot Table chart

So I would like to use the dimension limits option as for the straight table, but it is not available for the pivot table.

maybe there is another way to do that ?

I need Pivot table because I have 2 dimensions : details grouped by class (see attach file)

Best regards

Jacques

Re: TCD : sum values when they are < 1

ignacioroman — Wed, 29 Apr 2020 13:41:46 GMT

Hello, this option must work

If it doesn't, let me know and I'll do a test with a sample.

Regards!

Re: TCD : sum values when they are < 1

jleraille — Thu, 30 Apr 2020 08:28:30 GMT

Hello,

Sorry but (unless i'm wrong) the dimension limits option is not available for the pivot table.

Bests regards

Jacques

Re: TCD : sum values when they are < 1

jleraille — Mon, 04 May 2020 09:38:19 GMT

Hello,
I found a solution

Dimensions:
I calculate the total by an aggregation on the Class and Detail dimensions.
If <1 I take the fictitious dimension 'Other Class' otherwise the Detail dimension

Expression:
If dimension length Detail = 0 (fictitious Class) I accumulate the value of any dimension otherwise simple accumulation