Firstsortedvalue and count

[tommyl]

Hi experts,

I have a table that shows segments of the customers with validity start date and validity end dates like,

customerid	segment	validitystartdate	validityenddate
1	newcomer	01.01.2020	31.12.2020
1	loyal	01.01.2021	31.12.2021
2	premium	01.01.2020	31.12.2020
2	loyal	01.01.2021	31.12.2021

What i am trying to do is to count the customers according to their segments for a specific as-of date.

For instance if as-of date is 17-03-2021 (=vAsOf) counts should be:

newcomer:0

loyal:2

premium:0

What i did is in a KPI object for , say, "Loyal":

measure:

count({$<segment = {'loyal'},vAsOf={">=$(=firstsortedvalue( validityenddate= {'>$(=vAsOf)'},validitystartdate= {'<$(=vAsOf)'} >} validitystartdate,-validitystartdate))"}>}distinct [customerid])

But it is not right. 

Could you please help me on the problem?

Regards,

[tommyl]

Hi,

Those who might have the same problem, i fixed the issue by changing the places of the variable and the validity dates in the set expression as below:

count({<segment = {'loyal'},validitystartdate={"<=$(=vAsOf)"},validitystartdate={">=$(=vAsOf)"}>} distinct [customerid])

Regards,

Tommy