Set analysis

[Luben]

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. 

[joaopaulo_delco]

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
)

[JGMDataAnalysis]

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.

[Luben]

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

[JGMDataAnalysis]

How the variables vstart_date and vend_date are defined?

[Luben]

i have the like this 

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

[JGMDataAnalysis · Accepted solution]

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

[JGMDataAnalysis]

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

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

[Luben]

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();

[Digvijay_Singh]

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.