Firstsortedvalue and count

tommyl — Sat, 16 Nov 2024 00:25:15 GMT

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,

Re: Firstsortedvalue and count

tommyl — Sat, 10 Apr 2021 16:28:57 GMT

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

topic Re: Firstsortedvalue and count in App Development

Firstsortedvalue and count

Re: Firstsortedvalue and count