Count if with multiple conditions

[ioannagr]

Hello everyone,

I want to make a KPI that counts distinct customers for the nested if conditions below.

I figured this out but it gets '-' .

count(if(orderstatus={'0'} or orderstatus={'1'} or orderstatus={'2'} and len(string)='12' ,
if(sum(aggr(count(distinct(orderdate)), [customer]))>='2' and aggr(max(orderdate),[customer])>=Addmonths(Today(),-12) and aggr(max(orderdate),[customer])<=(Today())) distinct [customer])

Looking forward to the correct syntax.

Thanks.

[StarinieriG]

Hi,

 try in this way:

Count(distinct if(
Match(orderstatus,'0','1','2')>0 and Len(string)=12 and Aggr(count(distinct orderdate), [customer])>=2
and aggr(max(orderdate),[customer])>=Addmonths(Today(),-12)
and aggr(max(orderdate),[customer])<=(Today()), [customer]))

[sunny_talwar]

Try this

Count(DISTINCT {<
    [orderstatus] = {'0', '1', '2'},
    [string] = {"=Len(string) = 12"},
    [customer] = {"=Count(DISTINCT orderdate) >= 2 and Max(orderdate) >= AddMonths(Today(), -12) and Max(orderdate) <= Today()"}
>} [customer])

[ioannagr]

Works perfectly Sunny, but I forgot this one ;

I need this condition for each customer as well :

interval(max(orderdate)-date(max(orderdate,2)),'m')<='24'

Is it safe to add it under [customer] conditions, or do i need to do

interval(aggr(max(orderdate),[customer])-aggr(date(max(orderdate,2)),'m'), [customer])<='24'   ?

[sunny_talwar]

I think within the list of condition on customer is where it should be added

Count(DISTINCT {<
    [orderstatus] = {'0', '1', '2'},
    [string] = {"=Len(string) = 12"},
    [customer] = {"=Count(DISTINCT orderdate) >= 2 and Max(orderdate) >= AddMonths(Today(), -12) and Max(orderdate) <= Today() and (Max(orderdate)-Max(orderdate, 2)) <= 24"}
>} [customer])

[ioannagr]

Hi Sunny and thank you.

I just changed it to :

interval( (date(max(orderdate),'DD/MM/YYYY')- date(max(orderdate,2),'DD/MM/YYYY')),'m') <= '24'

because i wanted the difference in months.

[sunny_talwar]

I don't think this is giving you the difference in month, is it?

Interval((Date(Max(orderdate),'DD/MM/YYYY')- Date(max(orderdate,2),'DD/MM/YYYY')),'m')

I think this would be in minutes

[ioannagr]

Okay so the default date difference is in months?

And i have another question which i am positive you can solve 🙂

Let's say i have

count{

[field1]={'such'],

[field2]={'such'},

[field3={("=count(field4)={0}, field4={ set analysis1}) AND "=count(field4)={1}, field4={ set analysis2}) }

distinct [field]}

Is something like that possible?

[sunny_talwar]

I am not really sure I understand this... can you elaborate on this new requirement please

[ioannagr]

Okay let me see if I can rephrase my other question.

If a had a field

[customer]

as above and i had count(distinct(orderdates)=0 which needed to happen in the last year ( I figured it should be in the last 12 months with addmonths but definitely a date before today)

and a count(distinct(orderdates)>=1 which needed to happen in the last  2 years, 

could i do for this field:

count{

[field1]={'such'],

[field2]={'such'},

[customer]={("=count(distinct(orderdate))={'0'}, orderdate={ [<=$(=Date(Addmonths,(Today(),-12),'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))}) AND "=count(distinct(orderdate))>={'1'}, field4={ [<=$(=Date(Addmonths,(Today(),-24),'MM/DD/YYYY'))<=$(=Date(Today(),'MM/DD/YYYY'))}) }) }

distinct [field]}

[customer]

Thank you in advance.