Set Analysis when using Link Table to harmonize Date fields

Eddy — Thu, 10 Oct 2019 17:14:48 GMT

Hello dear experts,

I have a Fact Table with several different Date fields.

Now I want to use a Link table to be able to use only one date field "DATE" in the data model with "DateTyp" to refer to the correct date.

Problem: How to adjust the set analysis to have the same results in the KPI boxes -> Max(DATE) now returns the global maximum over the whole set of values in the DATE field.

!Simple Example QVD attached!

Script:

Facts:
Load * Inline [
ID, Status, Num, DateA, DateB, DateC
1, A, 10, 43748, ,
2, A, 20, 43749, ,
3, A, 30, 43750, ,
4, B, 20, , 43748,
5, B, 20, , 43749,
6, B, 30, , 43750,
7, B, 40, , 43751,
8, B, 50, , 43752, 43800
9, C, 30, , , 43801
10, C, 40, , , 43802
];

LinkTable:
Load
ID,
DateA as DATE,
'DateA' AS DateTyp
Resident Facts
Where Len(trim(DateA)) > 0 ;

For Each a in 'DateB', 'DateC'
Concatenate(LinkTable)
Load
ID,
$(a) as DATE,
'$(a)' as DateTyp
Resident Facts
Where Len(trim($(a))) > 0;
Next a

BEFORE:

KPI Objects like:

sum(
{<
Status = {'A'}
,DateA = {"$(=max(DateA))"}
>}
Num
)

NEW:

sum(
{<
Status = {'C'}
,DateTyp = {'DateC'}
,DATE = {"$(=max(DATE))"}
>}
Num
)

-> I want DATE = {"$(=max(DATE))"} only for the date set where Status = {'C'} AND DateTyp = {'DateC'},

what could be the possible solution?

Kind Regards

Eddy

Re: Set Analysis when using Link Table to harmonize Date fields

sunny_talwar — Thu, 10 Oct 2019 17:19:51 GMT

Try this

sum( {< Status = {'A'} ,DateTyp = {'DateA'} ,DATE = {"$(=max({<DateTyp = {'DateA'}>}DATE))"} >} Num )

Re: Set Analysis when using Link Table to harmonize Date fields

Eddy — Fri, 11 Oct 2019 07:42:02 GMT

Thank you very much, that sorted out my issue!

topic Re: Set Analysis when using Link Table to harmonize Date fields in App Development

Set Analysis when using Link Table to harmonize Date fields

Re: Set Analysis when using Link Table to harmonize Date fields

Re: Set Analysis when using Link Table to harmonize Date fields