Set analysis using concat,aggr and only

[#1]

Hi there

I have a set of data that shows like below

actdate	suppgroup	No.
08/06/2014	A	2
08/06/2014	B	16
08/06/2014	E	149
08/06/2014	C	17
08/06/2014	D	5
09/06/2014	A	0
09/06/2014	B	16
09/06/2014	E	149
09/06/2014	C	31
09/06/2014	D	11
10/06/2014	A	0
10/06/2014	B	16
10/06/2014	E	152
10/06/2014	C	28
10/06/2014	D	8

im using the following formula to display the last 3 days of data.

For Yesterday, =concat(aggr(only({$<[actdate]={">=$(=today()-1)"}>}Active),suppgroup),',')
-> This worked

For 2daysgo, =concat(aggr(only({$<[actdate]={">=$(=today()-2)"}>}Active),suppgroup),',')
-> This doesnt work

For 3daysago, ==concat(aggr(only({$<[actdate]={">=$(=today()-3)"}>}Active),suppgroup),',')
->This doesnt work.

Any idea
anyone?

[Gysbert_Wassenaar]

The only function can only return one value. If you select more dates with >= today()-2 you also get more values. And in that case the only function will return nulls.

[#3]

Thank you for your quick reply Gysbert.

For 2daysgo, =concat(aggr(only({$<[actdate]={">=$(=today()-2)"}>}Active),suppgroup),',')

im getting some values but not for all the suppgroup...

If i use =concat(aggr(only({$<[actdate]={'09/06/2014'}>}Active),suppgroup),',') it works but how come it doesnt work when i tried today()-2 ?

Im designing a dashboard that refresh the information every 5mins, it is not practical to hardcode the actual date hence im trying to make it dynamic.

i hope someone outthere could help me

[Anonymous]

Try Something like this:

For 2 days ago:

=concat(aggr(only({$<[actdate]={">=$(=Date(today()-2))"}>}Active),suppgroup),',')

For 3daysago:

=concat(aggr(only({$<[actdate]={">=$(=Date(today()-3))"}>}Active),suppgroup),',')

[#5]

Thanks for your reply Karthigayan.

I did come up with that formula as well but it is still not working. Im not really sure what's happening. It is displaying the values but not for all suppgroup... very odd.

[Anonymous]

Try it with single quotes instead of double quotes.

For 2 days ago:

=concat(aggr(only({$<[actdate]={'>=$(=Date(today()-2))'}>}Active),suppgroup),',')

[jonathandienst]

Gysbert explained the problem. If the Only function gets two or more possible values (which is true for all or most of the suppgroups when you are looking at 2 or 3 days ago), then Only will return null.

Perhaps you could change

{$<[actdate]={">=$(=today()-2)"}>}

to

{$<[actdate]={"$(=today()-2)"}>}

Or else you will need to take a different approach.

HTH

Jonathan

[#8]

Still the same Karthigayan. Im getting All suppgroup but the values are not showing for all. Out of 11, only 3 suppgroup values are showing.

[#9]

Thanks for the input Jonathan.

That didnt work either.

Im quite new on qlikview, in fact i learned pretty much everything from this site. Is there any other expression i could use to achieve my result?

[jerem1234]

why don't you concat in the aggr as well? Since as was described, they will be returning more than one value. Try:

=concat(aggr(concat({$<[actdate]={">=$(=today()-2)"}>}Active,','),suppgroup),',')

Hope this helps!