Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Why are you using single quotes around Year? May be try this
Count({$<CreatedYear = {"<=$(=Max(Year))"}, ClosedYear = {">=$(=Max(Year))"}>} DISTINCT OutletID)
Why are you using single quotes around Year? May be try this
Count({$<CreatedYear = {"<=$(=Max(Year))"}, ClosedYear = {">=$(=Max(Year))"}>} DISTINCT OutletID)
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
May be this
Count({$<OutletID = {"=CreatedYear <= Max(Year) and (ClosedYear >= Max(Year) or Len(Trim(ClosedYear)) = 0)"}>} DISTINCT OutletID)
This does not work. The KPI keeps trying to Load but no output.
How to write or in set analysis ?
Do you have a sample you can share? or can be done like this
{<Set1>+<Set2>}
Null has been stored as 0 for CloseMonthYear.
Count({$<
CreatedMonthYear= {"<=$(=Max(VMonthYear))"},
CloseMonthYear = {">=$(=Max(VMonthYear))"}
> +
<
CreatedMonthYear= {"<=$(=Max(VMonthYear))"},
CloseMonthYear = {'0'}
>
} DISTINCT OutletID)
So, this worked?
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)
Sorry, I am not sure I understand what is the problem with the expression?