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?

[#2]

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

[jerem1234]

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!

[fsimoes81]

With 3 day:

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

[#5]

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.

[jerem1234]

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.

[#7]

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.

[#8]

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

[jerem1234 · Accepted solution]

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

[fsimoes81]

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

[#11]

THANK YOU VERY MUCH. it works!