Displaying next sorted value where field is not empty

[FakeJupiter]

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:

1. We did not manage to find a solution how to achieve what I wanted in the chart settings without stepping into script. However, I got close. Here's a chart expression that worked for me in a simple sample file, but didn't for some reason work in my large and complex target file. In my target file it only returned null values. I figured it had something to do with the use of Aggr function.
   
   =FirstSortedValue(Distinct if(isnull(Category)or len(Category)<1,null(),Category),-Aggr(Distinct Date, Category))
2. With the help of veidlburkhard I managed to develop a solution via script. I'll post it in this thread.

[FakeJupiter]

This doesn't really work for me, because I need to only have each name appear once. In your example John appears twice. Basically, what I need is a list of all names (unique) and their most recent category, which is not empty, but an actual category.

In the actual file I'm developing, I seek to show the list of all car services and the category they belong to. This category can change over time; therefore, I need to show the latest category. However, in some cases the latest entry / entries for the car service have category field missing, so I at least would like to assign the latest available category the car service had in the previous records (lines).

Thank you for your answer.

[Arthur_Fong]

Try this in calculated dimension:

=aggr(FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date),Name)

[FakeJupiter]

Still doesn't work.

I made a simple load for the example as follows:

Data:
Load * inline [
Name|Category|Date
John||06.06.2019
Chad|B|05.05.2019
John|C|04.04.2019
John|D|03.04.2019
Chad||01.01.2019
Chad|U|29.12.2018
] (delimiter is '|');

The result is as follows:

If I check "Suppress when value is null" at the Dimension tab for the calculated dimension, "John" disappears.
At Presentation tab all "Suppres Zero-Values" are checked.

[Arthur_Fong]

Try this at calculated dimension:

=aggr(FirstSortedValue(Category,if(ISNULL(Category)or len(Category)<1,null(),-Date)),Name)

[FakeJupiter]

This seems to work in the example load and table, but for some reason doesn't work in the actual file I need it to work in.

I checked all "Suppress Zero-Value" boxes, they all match.

I don't know what else to look for. In the main file, I also use 2 dimensions - Serviss (analogue to Name) and the calculated dimension that you posted. Naturally, I replaced all the field names as necessary. I have only 1 Expression that is "=count([Lietas Nr.])" which basically is the same thing as "=count(Date)". Here is my version of the calculated dimension with the appropriate field names for the main file:

=aggr(FirstSortedValue([Servisa prioritāte],if(ISNULL([Servisa prioritāte])or len([Servisa prioritāte])<1,null(),-[Saskaņošanas datums])),Serviss)

In case you might find it of any use, I posted the tables from the main file below. On the left I have the table where I used the simple expression from my original post to show category ("Servisa prioritāte"), and on the right I have the table that uses the calculated dimension you provided. Obviously, the tables look the same, unfortunately.

EDIT: What I just noticed is that in some cases it actually does work. In the table above to the right one row has what I need in the category (Servisa prioritāte) field. I can't figure out why it doesn't work in other cases though.

[Arthur_Fong]

Check if your date is text or numeric format.

Create a listbox and screenshot your date column.

[veidlburkhard]

Hi Hoodoo,

why not doing it simple like this:

If(IsNull(Category),FirstSortedValue(Category,-Date, 2), FirstSortedValue(Category,-Date))

and Count(Name)

See the picture:

Happy qliking

Burkhard

[FakeJupiter]

All dates seem to be in date format. I checked through the whole list box and the source file.

I have a suspicion that it might have something to do with the data from the source file. In one of the cases where your provided calculated dimension doesn't work, it, as well as the short formula from my original post should work, as the source file did not have latest by date category field empty. So why does it return a "-"?

[FakeJupiter]

Hi!

Thanks for your answer.

I encountered two problems with this approach.

One is that, if I understood the function properly, it wouldn't work if the second newest (by Date) Category would also be empty. In my real case not example I have some cases where in the source file there are, for instance, a total of 500 rows with the same Name, but the latest 50 don't have a category.

The second is that for some reason it works on the sample table I provided in my original post, but doesn't work on the example I provided afterwards.

When I have load as follows:

Data:
Load * inline [
Name|Category|Date
John||06.06.2019
Chad|B|05.05.2019
John|C|04.04.2019
John|D|03.04.2019
Chad||01.01.2019
Chad|U|29.12.2018
] (delimiter is '|');

it returns this:

[veidlburkhard · Accepted solution]

Hi Hoodoo,

if this is so, you have to calculate a Category Flag in your script in order to identify the empty categories:

LOAD
Name,
Category,
If(IsNull(Category) or Len(Category) = 0, 0, 1) as FlagCat,
Date;
Load * inline [
Name|Category|Date
John||06.06.2019
Chad|B|05.05.2019
John|C|04.04.2019
John|D|03.04.2019
Chad||01.01.2019
Chad|U|29.12.2018
] (delimiter is '|');

and use this flag in the set analysis of your expression.

See the picture:

Hope this solves your problems.

Best regards

Burkhard