topic Re: Year Comparison in App Development

Year Comparison

arjunmadhusudan — Fri, 06 Apr 2018 16:21:00 GMT

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)

Re: Year Comparison

sunny_talwar — Fri, 06 Apr 2018 16:33:53 GMT

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

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

Re: Year Comparison

arjunmadhusudan — Fri, 06 Apr 2018 16:42:57 GMT

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

Re: Year Comparison

sunny_talwar — Fri, 06 Apr 2018 16:45:48 GMT

May be this

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

Re: Year Comparison

arjunmadhusudan — Sun, 08 Apr 2018 02:34:03 GMT

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

How to write or in set analysis ?

Re: Year Comparison

sunny_talwar — Sun, 08 Apr 2018 03:01:13 GMT

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

{<Set1>+<Set2>}

Re: Year Comparison

arjunmadhusudan — Sun, 08 Apr 2018 03:08:38 GMT

Null has been stored as 0 for CloseMonthYear.

Count({$<

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

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

> +

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

CloseMonthYear = {'0'}

} DISTINCT OutletID)

Re: Year Comparison

sunny_talwar — Sun, 08 Apr 2018 03:50:14 GMT

So, this worked?

Re: Year Comparison

arjunmadhusudan — Mon, 09 Apr 2018 07:34:24 GMT

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)

Re: Year Comparison

sunny_talwar — Mon, 09 Apr 2018 18:34:17 GMT

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