Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I’m looking to find a solution to the following problem:
I have a data like this:
Table1:
EventId | Date | Detail | SomeFields | … |
1 | 01.10.2021 | HNT |
|
|
2 | 02.10.2021 | SMA |
|
|
3 | 03.10.2021 | KFN |
|
|
4 | 04.10.2021 | HNT |
|
|
Table2:
EventId | ErrMarker | ErrValMin | ErrValMax | ErrText |
1 | 1 | 0.5 | 7 | sometext |
1 | 5 | 2 | 3 | sometext |
2 | 10 | 3 | 3.1 | sometext |
2 | 8 | 1 | 2 | sometext |
2 | 1 | 0.5 | 7 | sometext |
3 | 15 | 1.2 | 1.3 | sometext |
3 | 8 | 1 | 2 | sometext |
4 | 1 | 0.5 | 7 | sometext |
4 | 17 | 1.3 | 1.4 | sometext |
At the result I need to make a straight table at my sheet:
ErrMarker | ErrValMin | ErrValMax | ErrText | Repeated in events
=Count(distinct EventId) | Last date happened
= Max(Date) | Total events after last date = ??? | Total events
=Count(total distinct EventId) |
1 | 0.5 | 7 | sometext | 3 | 04.10.2021 | 0 | 4 |
5 | 2 | 3 | sometext | 1 | 01.10.2021 | 3 | 4 |
8 | 1 | 2 | sometext | 2 | 03.10.2021 | 1 | 4 |
10 | 3 | 3.1 | sometext | 1 | 02.10.2021 | 2 | 4 |
15 | 1.2 | 1.3 | sometext | 1 | 03.10.2021 | 1 | 4 |
17 | 1.3 | 1.4 | sometext | 1 | 04.10.2021 | 0 | 4 |
The question is what set expression I need to count “Total events after last date”. (This measure shows how many events(in total) happens after current ErrMarker last appearance).
Sorry for my English, it's not so good.
Thanks!
Hi @Artem_Dyrenko ,
Please check the attached QVF file.
Thanks for the answer!
It works for that simple example, but it doesn't fit if we skip days or if we have several events at one day.
Ooh.. I will send you updated QVF with new expression