Year Comparison

[arjunmadhusudan]

I am using below equation to find shops opened before selected year and closed after selected year.

CreatedYear  is the year of opening year of shop

ClosedYear is the year of closing year.

As multiple year selection can be made in filter, we have used max to find the latest year selected.

Also how to add shops/ outlets which are having closedyear = NULL

Count({

$<

CreatedYear = {"<= $(max('Year'))"},

ClosedYear = {">= $(max('Year'))"}

>  } Distinct OutletID)

[sunny_talwar · Accepted solution]

Why are you using single quotes around Year? May be try this

Count({$<CreatedYear = {"<=$(=Max(Year))"}, ClosedYear = {">=$(=Max(Year))"}>} DISTINCT OutletID)

[arjunmadhusudan]

Ignore single quote , copy paste typo.

The value I m getting using above equation does not match when I hard code the required year.

Also how do I add or inside set analysis.

I need :   Closedate is null or closedate > Year

[sunny_talwar]

May be this

Count({$<OutletID = {"=CreatedYear <= Max(Year) and (ClosedYear >= Max(Year) or Len(Trim(ClosedYear)) = 0)"}>} DISTINCT OutletID)

[arjunmadhusudan]

This does not work. The KPI keeps trying to Load but no output.

How to write or in set analysis ?

[sunny_talwar]

Do you have a sample you can share? or can be done like this

{<Set1>+<Set2>}

[arjunmadhusudan]

Null has been stored as 0 for CloseMonthYear.

Count({$<

CreatedMonthYear= {"<=$(=Max(VMonthYear))"},

CloseMonthYear = {">=$(=Max(VMonthYear))"}

> +

<

CreatedMonthYear= {"<=$(=Max(VMonthYear))"},

CloseMonthYear = {'0'}

>

} DISTINCT OutletID)

[sunny_talwar]

So, this worked?

[arjunmadhusudan]

It worked.

However I have also  to compare monthyear which is another filter which is in MMMYYYY format...

CreatedMonthYears and CLoseMonthYear are alos in MMMYYYY format and  MonthYears is also in MMMYYYY format.

However I don't think MMMYYYY comparison works the year to year comparison id done in case of CloseYear.

Need help here.

Count({

$<

CreatedYear = {"<=$(=max(Years))"},

ClosedYear = {">$(=max(Years))"},

CreatedMonthYear = {"<$(=max(MonthYears))"},

CloseMonthYear = {">$(=max(MonthYears))"}

+ <

CreatedYear = {"<=$(=max(Years))"},

ClosedYear = {'0'},

CreatedMonthYear = {"<$(=max(MonthYears))"},

CloseMonthYear = {'0'}

>

} Distinct Outletid)

[sunny_talwar]

Sorry, I am not sure I understand what is the problem with the expression?