Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there
I have a set of data that shows like below
actdate | suppgroup |
|
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?
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),',')
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.
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
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),',')
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.
Try it with single quotes instead of double quotes.
For 2 days ago:
=concat(aggr(only({$<[actdate]={'>=$(=Date(today()-2))'}>}Active),suppgroup),',')
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
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.
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?
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!