Showing value with last date where value is not blank or zero

[jlmorgado]

Hi,

I have a table with the following structure. It can have multiple entries per date, with different types and values.

Date                    Type          Value

01/12/2023       A                 100

01/12/2023       B                 200

01/12/2023       C                 150

02/12/2023       A                 101

02/12/2023       B                 178

03/12/2023       A                 105

03/12/2023       B                 170

I do need to have the latest value which is not zero per type, using set analysis (using month as a dimension) and also the latest value per type to use in a text box. Tried a couple of approaches using the FirstSortedValue and MAX but none seem to be working. I'm sure it's my rusty QV knowledge but can't seem to get a working set analysis expression that gives me what I need... not to mention the text box expression.

Anyone with free time to give me some help here?

Thanks!

[BrunPierre]

What's the expected result?

[jlmorgado]

Hi Brun,

The expected result would be to have:

For A - 105 (related to the last record in the table - 03/12/2023)

For B - 170 (related to the last record in the table - 03/12/2023)

For C - 150 (related to the last record in the table - 01/12/2023)

Thanks,

J

[BrunPierre]

Table:
LOAD Date,
Type,
Value
FROM ...;

Inner Join
load Type,
Max(Date) as Date
Resident Temp
Group By Type;

[jlmorgado]

Thanks for you reply Brun.

I think I missed to explain it properly. What I'm looking for is some set analysis expression that allow the last value of a period (month, tax year, etc) to be showed on some charts. Also looking to find some expression to be able to find the latest value for a particular type, so I can put that value in a text box.

So reformulating my example:

Date                    Type          Value

01/10/2023       A                 100

04/10/2023       B                 200

04/10/2023       C                 300

05/11/2023       A                 110

21/11/2023       B                 205

22/11/2023       C                 305

24/11/2023       C                 0

01/12/2023       A                 100

02/12/2023       A                 101

02/12/2023       B                 178

03/12/2023       C                 200

04/12/2023       A                 150

04/12/2023       B                 101

05/12/2023       B                 105

05/12/2023       C                 170

06/12/2023       A                 0

06/12/2023       C                 0

We would get:

table values:

October

A - 01/10/2023 - 100

B - 04/10/2023 - 200

C - 04/10/2023 - 300

November

A - 05/11/2023 - 110

B - 21/11/2023 - 205

C - 22/11/2023 - 305

December

A - 04/12/2023 - 150

B - 05/12/2023 - 105

C - 05/12/2023 - 170

And for the text box, we would just get the latest non-zero entry for all the types:

A - 04/12/2023 - 150

B - 05/12/2023 - 105

C - 05/12/2023 - 170

Maybe I should just create some table with the latest value per month, so that I can just use it easily?

Thought this would be easier to pull, as the dimension would filter the values by month for example and then we would just need the max date for each one of the types 😞 

J.

[BrunPierre · Accepted solution]

=Concat(DISTINCT Aggr(DISTINCT If(Date = Max({<Value -= {'0'}>}TOTAL <Type > Date) ,CombinedFields), Date, Type), ', ')

=FirstSortedValue({<Value -= {'0'}>} Value, -Date)

Script:

YourTable:
LOAD Type&' - '&Date&' - '&Value as CombinedFields,
Month(Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY')) as Month,
Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Date,
Type,
Value
INLINE [
Date, Type, Value
01/10/2023, A, 100
04/10/2023, B, 200
04/10/2023, C, 300
05/11/2023, A, 110
21/11/2023, B, 205
22/11/2023, C, 305
24/11/2023, C, 0
01/12/2023, A, 100
02/12/2023, A, 101
02/12/2023, B, 178
03/12/2023, C, 200
04/12/2023, A, 150
04/12/2023, B, 101
05/12/2023, B, 105
05/12/2023, C, 170
06/12/2023, A, 0
06/12/2023, C, 0
] ;

[jlmorgado]

Thanks Brun!