I think you can easily solve your problem by putting in Dimension of your graph Status, and in Expression a Count(ID).
You only have to check the box Relative at the right side of the Expression.
You can have a look at my quick test app joined to this post.
test.qvw 150.8 K
Thanks for your reply but, no, that is not really what I wanted. You see the status 1,3,4,5 is all for the item with ID=ID2. So what I want really is to display the LATEST status for each item (i.e. max status / ID or status with the max date / ID).
I.s. the pie chart would only have two areas in this case (50% each), one being status = 2, one being status = 5.
Ok I see, it was not so simple after all.
I did something real quick but I am not sure I fully understood what you want.
Hope that helps anyway.
test.qvw 152.8 K
Hi Dau & thanks again!
I think you may have misunderstood. In your example you work on the ID and date columns for selections/filtering but that is not really possible as I don't know the ID set beforehand.
So what I need is one of:
1) A solution that only works on the Status field (i.e. somehow counts the number if distinct IDs but only where Status is the max for each count).
2) A solution similar to 1) but where the not max of status is used for filtering but instead finding the status where with the max Date.
Hope this clarifies? I.e. there couldn't be any "input parameters" to the expression.
Just to clarify even more, I could live with managing this during the loading as well (if e.g. adding new tables / columns could help for this scenario).
Indeed in my solution the ID is not dynamic which is a bad limitation of my formulas.
I did tried :
- aggr (max(Status),ID) this give the right status but it's not the right one
- aggr (max(date(Date,'YYYY-MM-DD')),ID) this gives the right date but I guess this is not useful like that.
I think your solution would be to mix both formulas so that you can find for the Max(Date) for each ID the associated status.
I didn't have time to find the solution myself.
Sorry for that and hope you'll find the way to solve your issues
I did a bit of a test working in the load script but my experience fails here too but maybe someone could assist here?
LOAD * Inline [TestID, TestDate, TestStatus
ID1, 2013-01-01, 1
ID1, 2013-01-02, 2
ID2, 2013-01-01, 1
ID2, 2013-01-02, 3
ID2, 2013-01-03, 4
ID2, 2013-01-04, 5
LOAD TestID as TID1, TestStatus as TS1
Order by TestID, TestStatus DESC;
TID1 as TheID,
if(TS1 < previous(TS1), Previous(TS1), TS1) as StatusText
My problem here is that using comparisons with previous only work 1 level back... So can I either check more records back (fixed number or in some way for all status' based on the same ID?)