Aggr expression issue

[jduluc12]

Hi,

I got an expression. A part of it is working.

I just want to know if i am violating an rules here.

if (ValueList('0', '1', '2', '3', '4') = '0',

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1]) = 0, 1)),

if (ValueList('0', '1', '2', '3', '4') = '1',

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1]) = 1, 1))

))

The dimension is a value list dimension. there are 5 dimensions (0 to 4) but I am using only 2 in this expression.

The main expression will be repeated for each dimension.

The main expression is:

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1])

If the value of sum is 0 then it has to go in first dimension and if the value is 1 then it has to show up for the second dimension.

This expression works for the first dimension but does not work for the second dimension. It does not show any value.

I am not sure what is going wrong with it. Is it wrong to have "aggr" statements used twice in these kind of expressions?

Thanks

Jean

[vishsaggi]

Your expr seems right. Can you share some sample data to look into or a qvw file you are working on?

[jduluc12]

I will try to build some sample data.

[andrey_krylov]

As I understood there are some nuances in combining  valuelists and aggregation. Try to add a NODISTINCT

if (ValueList('0', '1', '2', '3', '4') = '0',

sum(if(aggr(NODISTINCT count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1]) = 0, 1)),

if (ValueList('0', '1', '2', '3', '4') = '1',

sum(if(aggr(NODISTINCT count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1]) = 1, 1))

))

And see this post

https://qliktech.hosted.jivesoftware.com/message/851813#851813

[jduluc12]

the second dimension shows up but it seems to bring the wrong data.

Let me read more about nodistinct .

[andrey_krylov]

See these

Re: Referring from aggr() to ValueList() value

Re: Aggr function on Valuelist

It seems you'd better to replace a valuelist with an island table (dimension).

[jduluc12]

Now I am using this dimension

aggr(

if(count(DISTINCT{<ATxn.aType={'ABC'}},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

[ATxn.ID]) = 0, Dual('0', 1),

if (count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

[ATxn.ID]) = 1), Dual('1',2)) ,

[ARN.ID_1])

and expression is

count(DISTINCT{<[ARN.Type]={'Order'}>}

if (Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235

[ARN.ID_1]))

But it still shows nothing.

Any idea, if there is anything wrong with it.

[andrey_krylov · Accepted solution]

I think the idea is to load an island table

LOAD * Inline [

  DummyDim

  0

  1

  2

  3

];

then use it nstead of a ValueList and aggrigate by this dimension

if (DummyDim = 0,

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1], DummyDim) = 0, 1)),

if (DummyDim = 1,

sum(if(aggr(count(DISTINCT{<ATxn.aType={'ABC'},

[ARN.Type]={'Order'},

[ATxn. Name]={'ABCtoDEF'},

[ARN.Taken]={0}>}

if (ARN.Type='Order'

and Interval(Today() - [ARN.Date], 'd') >= 0 and Interval(Today() - [ARN.Date], 'd') < 235,

[ATxn.ID])), [ARN.ID_1], DummyDim) = 1, 1))

))

[jduluc12]

Thanks to helping me out on this.

I learned something new.