I have a table in my source file, say, like this (except not sorted by Date):
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:
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!
if this is so, you have to calculate a Category Flag in your script in order to identify the empty categories:
If(IsNull(Category) or Len(Category) = 0, 0, 1) as FlagCat,
Load * inline [
] (delimiter is '|');
and use this flag in the set analysis of your expression.
See the picture:
Hope this solves your problems.
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.
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.
I tried doing that, it didn't work.
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.
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:
Suppress null value at presentation layer.
Thanks and regards,