Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have a table in my source file, say, like this (except not sorted by Date):
Name | Category | Date |
John | 07.07.2019 | |
Anthony | A | 06.06.2019 |
Chad | A | 05.05.2019 |
Michael | C | 04.04.2019 |
Hillel | B | 03.03.2019 |
Josh | C | 02.02.2019 |
John | D | 01.01.2019 |
John | B | 12.12.2018 |
What I want to achieve is to show the latest category in a chart (straight table) for every Name (which would be the dimension). I figured how to do this by using this expression:
=FirstSortedValue(Category,-Date)
The problem that I encountered is that, as I depicted in the table above, some names don't have respective categories in the latest (by Date) entry / entries. What I would like the table to show is the latest category that is not empty. In this example for John category D would be shown (showed?).
Any help would be much appreciated. Thanks!
EDIT:
Thank you.
I'm sure this would work. Using this approach, is there any chance I could make a straight table, filtering out some of the categories, so that only, say, categories "A"and "C" would show up in the table and the names not belonging to said categories would not be included in the straight table?
Yes, Hoodoo, this filtering will work, see the picture:
Best regards
Burkhard
Sorry for not specifying, but is there a way to achieve that without making a selection / filter? Like you would when using a set analysis. My goal would be for the table to only show the list of the Names with certain categories without having to manually select them in filter.
this is easy to manage with set analysis, Hoodoo.
See the picture (only category B is shown, if you want to show several categories,
use a list instead, e.g. category = {'A', 'D', 'F'}):
I think I figured out one of the problems with using this chart expression that you provided:
=FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date)
In some cases it doesn't work, because the most recent date is not unique, and the function doesn't know which value to choose; therefore, it returns null.
Here's what worked for me in the example file:
=FirstSortedValue(Distinct if(isnull(Category)or len(Category)<1,null(),Category),-Aggr(Distinct Date, Category))
However, it again does not work in the main file. I get null values next to every name (Serviss). It has something to do with the Aggr / Date part of the expression, I'm sure.
I made my a custom solution using a similar approach to what you suggested. I'll post it in this thread as well, if you are interested.
Anyways, thanks for your answer!
Here's the final solution I used. Basically you can get the idea from the script.
TamesTemp:
Mapping LOAD
Name,
Max(Date) as Max_Datums
Resident tmpTamesT where len(Category)>2
Group by Name;
LastSegment:
NoConcatenate
LOAD Category,
Date(ApplyMap('TamesTemp',Name,0)) as Max_Datums,
Date - ApplyMap('TamesTemp',Name,0) as Filter_Value,
Name,
Date
Resident tmpTamesT;
LastSegment2:
Mapping LOAD Name,
Category
Resident LastSegment where Filter_Value = 0;
Drop table LastSegment;
ApplyMap('LastSegment2',Name,'nav segmenta') as Last_Segment
Then I used Last_Segment field in my chart. Hope somebody can make a use of this.