topic Re: Set analysis in App Development

Set analysis

Luben — Wed, 20 Oct 2021 18:18:17 GMT

Hello I need help converting the below IF statement to Set analysis;

COUNT(
DISTINCT IF(
Folder_Open_Date >=vstart_date AND
Folder_Open_Date <=vend_date AND
[Status]<>"V" AND [Status]<>"Void"

,[Claim]

,NULL
)

Thanks in Advance.

Re: Set analysis

joaopaulo_delco — Wed, 20 Oct 2021 18:58:05 GMT

Hi @Luben !

If the fields vstart_date and vend_date are variable you can try this:

Count( {<Folder_Open_Date={">=$(vstart_date)"},Folder_Open_Date={"<=$(vend_date)"},Status-={'V'},Status-={"Void"}>} Distinct Claim )

Re: Set analysis

JGMDataAnalysis — Wed, 20 Oct 2021 19:25:58 GMT

Try with this...

Count( {< [Folder_Open_Date] = {">='$(vstart_date)' <='$(vend_date)'"} ,[Status] *= E({<[Status] = {V, Void}>}) >} Distinct [Claim] )

If it doesn't works, try removing the single quotes.

Re: Set analysis

Luben — Wed, 20 Oct 2021 19:53:45 GMT

Thanks Guy's for your response, i tired both approaches and get zero in my KPI

Re: Set analysis

JGMDataAnalysis — Wed, 20 Oct 2021 20:04:15 GMT

How the variables vstart_date and vend_date are defined?

Re: Set analysis

Luben — Wed, 20 Oct 2021 20:16:15 GMT

i have the like this

Let Vend_date = today();
Let Vstart_date = AddMonths(MonthStart(Today()), -1);

Re: Set analysis

JGMDataAnalysis — Wed, 20 Oct 2021 20:35:43 GMT

Count(
{<
[Folder_Open_Date] = {">='$(Vstart_date)' <='$(Vend_date)'"}
,[Status] *= E({<[Status] = {V, Void}>})
>}
Distinct [Claim]
)

Re: Set analysis

JGMDataAnalysis — Wed, 20 Oct 2021 20:45:04 GMT

The Vstart_date variable can be defined a bit more compactly...

LET Vstart_date = MonthStart(Today(), -1);

Re: Set analysis

Luben — Thu, 21 Oct 2021 13:09:14 GMT

Count(
{<
[Folder_Open_Date] = {">='$(Vstart_date)' <='$(Vend_date)'"}
,[Status] *= E({<[Status] = {V, Void}>})
>}
Distinct [Claim]
)

Hello JGM I Tired the above and still getting zero in the KPI as seen below

I used this as my variables

LET Vstart_date = MonthStart(Today(), -1);

Let Vend_date = today();

Re: Set analysis

Digvijay_Singh — Thu, 21 Oct 2021 13:32:33 GMT

Can you share what do you see at the bottom of expression editor, the evaluated set expression appears over there, I guess something is wrong with the date format comparison in the set condition.

Re: Set analysis

JGMDataAnalysis — Thu, 21 Oct 2021 13:44:03 GMT

In addition to what @Digvijay_Singh asks you,
Could you share us the definition in script of the field [Folder_Open_Date] or a screenshot of what it looks like used in a chart?

Re: Set analysis

Luben — Thu, 21 Oct 2021 14:27:55 GMT

it's says ok at the bottom of the Edit expression.

that is the above date that show up

SET DateFormat= 'YYYY-MM-DD'; this my date format