topic Re: Set analysis using concat,aggr and only in QlikView

Set analysis using concat,aggr and only

Wed, 11 Jun 2014 13:10:28 GMT

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?

Re: Set analysis using concat,aggr and only

Gysbert_Wassenaar — Wed, 11 Jun 2014 13:18:40 GMT

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.

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 13:27:03 GMT

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

Re: Set analysis using concat,aggr and only

Anonymous — Wed, 11 Jun 2014 13:27:18 GMT

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),',')

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 13:35:48 GMT

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.

Re: Set analysis using concat,aggr and only

Anonymous — Wed, 11 Jun 2014 14:15:33 GMT

Try it with single quotes instead of double quotes.

For 2 days ago:

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

Re: Set analysis using concat,aggr and only

jonathandienst — Wed, 11 Jun 2014 14:24:13 GMT

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)"}>}

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

Or else you will need to take a different approach.

HTH

Jonathan

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 14:25:08 GMT

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.

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 14:31:09 GMT

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?

Re: Set analysis using concat,aggr and only

jerem1234 — Wed, 11 Jun 2014 14:36:37 GMT

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!

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 14:41:30 GMT

Jerem1234 its almost there!! however, i only need the values two days ago.

The result is showing, based on my example:

suppgroup No.

A 0,0

B 16,16

C 28,31

etc.

getting there but still not quite right

Re: Set analysis using concat,aggr and only

jerem1234 — Wed, 11 Jun 2014 14:48:58 GMT

So you want only the value for today()-2 and not for anything greater?

Then you should go with what Jonathan Dienst suggests but wrap it with the date function like:

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

Hope this helps!

Re: Set analysis using concat,aggr and only

fsimoes81 — Wed, 11 Jun 2014 14:52:42 GMT

With 3 day:

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

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 15:05:21 GMT

jerem1234, i got excited! im getting results. however when i look closely, the values showing are yesterdays, not 2 days ago.

i'm getting close, i think the problem is to do with the date. I had a look at the SQL data, the date format is showing like YYYY-MM-DD. do you think this is what's causing the data not to display correctly? i created a list box, the date format is showing the same as the SQL.

Re: Set analysis using concat,aggr and only

jerem1234 — Wed, 11 Jun 2014 15:12:04 GMT

Yes, certainly a possibility.

Make sure that the dates in your data match the same format when you do date(today()-2) in the set analysis. Therefore date(today()-2) will match one of your date formats in your data and will grab the correct values.

Make sure your data is in date format and not just string values as well.

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 15:17:33 GMT

Fabio Simoes thanks for your input.

Guys, i really appreciate all your contributions here. Just to clarify,the output i was expecting are as follow:

Suppgroup Yesterday 2daysago 3daysago

A 0 0 2

B 16 16 16

C 17 31 28

etc...

the expressions you guys have suggested are all very useful. however, the results are still not quite right.

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 15:31:32 GMT

So what should be the date format?

In the SQL shows, YYYY-MM-DD and the list box also shows YYYY-MM-DD.

i tried the following still the output is incorrect.

=concat(aggr(concat({$<[actdate]={">=$(=date(today()-2),'YYYY-MM-DD')"}>}Active,','),suppgroup),',') -> shows all concat values

=concat(aggr(only({$<[actdate]={">=$(=date(today()-2),'YYYY-MM-DD')"}>}Active,','),suppgroup),',') -> no values

Re: Set analysis using concat,aggr and only

jerem1234 — Wed, 11 Jun 2014 15:36:53 GMT

It should be the format that your dates in your data. If you go to table viewer or see what all the dates looks like, that should be the format you need.

Try:

=concat(aggr(only({$<[actdate]={"$(=date(today()-2, 'DD/MM/YYYY'))"}>}Active),suppgroup),',')

Re: Set analysis using concat,aggr and only

fsimoes81 — Wed, 11 Jun 2014 15:45:56 GMT

Ok, then Concat is not necessary, and, you must use =, not >=

Re: Set analysis using concat,aggr and only

Wed, 11 Jun 2014 16:06:16 GMT

THANK YOU VERY MUCH. it works!