Straight Table with value of 0, but showing the values when selected...

[samuel_lin]

Hi all,

I am experiencing something that's pretty difficult for me to solve, and I have thought this through over the weekend with no luck...

L18M...through L30D are all with the value of zero, with the expression:

sum(Aggr(Max({<LineType={'Shipped', 'Returned'}>} ExtRevenue), OrderLineNumber))

the same thing happens if I remove the Aggr function with the expression:

sum({<LineType={'Shipped', 'Returned'}>} ExtRevenue)

HOWEVER, if L18M is selected, the value will show up to be non-zero....

has anyone experienced this also?

Additional Information:

orderDate_Segment2 is defined in the script:

orderDate_Segment2:

Load if((today() - [order date])  > 730, [OrderNumber]) as OrderNumber,

'L24M+' as orderDate_Segment2

Resident [AgilOneTransactionExport];

Concatenate

Load if((today() - [order date])  <= 730 , [OrderNumber]) as OrderNumber,

'L24M' as orderDate_Segment2

Resident [AgilOneTransactionExport];

Concatenate

Load if((today() - [order date])  <= 548 , [OrderNumber]) as OrderNumber,

'L18M' as orderDate_Segment2

Resident [AgilOneTransactionExport];

Concatenate

Load if((today() - [order date])  <= 365 , [OrderNumber]) as OrderNumber,

'L12M' as orderDate_Segment2

Resident [AgilOneTransactionExport];

Concatenate

Load if((today() - [order date])  <= 275 , [OrderNumber]) as OrderNumber,

'L9M' as orderDate_Segment2

Resident [AgilOneTransactionExport];

Concatenate

Load if((today() - [order date])  <= 185 , [OrderNumber]) as OrderNumber,

'L6M' as orderDate_Segment2

Resident [AgilOneTransactionExport];

Concatenate

Load if((today() - [order date])  <= 90 , [OrderNumber]) as OrderNumber,

'L3M' as orderDate_Segment2

Resident [AgilOneTransactionExport];

Concatenate

Load if((today() - [order date])  <= 30, [OrderNumber]) as OrderNumber,

'L30D' as orderDate_Segment2

Resident [AgilOneTransactionExport];

[samuel_lin]

I figured out why...

BUT this shouldn't be the case.

basically L24M+ and L24M already get all the Order Numbers, and for some reason, Qlik suppress the values from showing for the the smaller segments?

L12M includes L18M, L12M, L9M... L30D...

any idea?

[Gysbert_Wassenaar]

If sum({<LineType={'Shipped', 'Returned'}>} ExtRevenue) returns 0 than you can be sure that the sum is really zero for that orderDate_Segment2 value. The expression with the aggr function aggregates on OrderLineNumber which doesn't seem to be a dimension of your straight table. I don't know your complete data model, but the aggr probably doesn't make any sense. Meaning you get bogus numbers even when they're not 0. You could try adding a nodistinct keyword in the aggr function, i.e. aggr(nodistinct ...etc). You'll probably see more bogus numbers, but selecting a orderDate_Segment2 value won't change the result of the expression anymore.

[samuel_lin]

thank you for your reply, Gysbert!

great ideas. I have aggr function because in the data, there are always two lines with the same OrderLineNumber and with the same revenue. any idea on how just to pick only one OrderLineNumber for the expression?

Thanks,

[Gysbert_Wassenaar]

Divide the result by two.