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:
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
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.
Try this:
FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date)
In AddOns > Data Handling > uncheck include zero values.
Sorry did that on QS.
In QV, presentation > suppress null values.
Thanks for the answer.
Is it really necessary to edit the AddOns settings to achieve this? I am developing this QV file for various people, and it has a lot of sheets, tables and charts. Would it be wise to change such a global setting?
I did, however, change the chart's settings in the Presentation tab to suppress zero values and used the script you provided. It didn't work unfortunately.
EDIT:
I tried doing that, it didn't work.
Did you apply this expression?
FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date)
I am working fine without null values displayed:
Yes. It gives me the same result as the basic formula in my original post.
Here's my chart presentation settings just in case. The column "Servisa prioritāte" = "Category" in my example.
Can you screenshot your dimensions and expressions tab?
Sure. In Dimensions "Serviss" equals "Name" in my example.
In Expressions "Servisa prioritāte" equals "Category" in my example.
I figured out that for your example to work, I must also:
Is there any way I can do this with only the Serviss (Name) dimension and having the count expression as well?
Posting the screenshots below:
I'll try adding screenshots to this post, but my last reply with screenshots got marked as spam for some reason.
Basically, I found out that your solution works, but I have to add Date to dimensions and I can't have a count expression in addition to the FirstSortedValue expression that counts the total entries (rows) in the data set.
Is there any way to achieve the same result, but without having to add Date dimension and having the additional count expression?
Serviss = Name
Servisa prioritāte = Category
In your solution also there will be several rows for the Name. However, what I would like to, is to have only one row for each name and for it to show the latest (by date) Category available, meaning the latest category entry for the Name that is not zero or empty.
Instead of adding the category as expression,
Create a calculated dimension and try this:
=aggr(FirstSortedValue(if(isnull(Category)or len(Category)<1,null(),Category),-Date),Date)
Suppress null value at presentation layer.
Thanks and regards,
Arthur Fong