Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
=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
] ;
What's the expected result?
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
Table:
LOAD Date,
Type,
Value
FROM ...;
Inner Join
load Type,
Max(Date) as Date
Resident Temp
Group By Type;
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.
=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
] ;
Thanks Brun!