Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

arjunmadhusudan
New Contributor II

Year Comparison

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)

1 Solution

Accepted Solutions

Re: Year Comparison

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

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

9 Replies

Re: Year Comparison

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

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

arjunmadhusudan
New Contributor II

Re: Year Comparison

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

May be this

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

arjunmadhusudan
New Contributor II

Re: Year Comparison

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

How to write or in set analysis ?

Re: Year Comparison

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

{<Set1>+<Set2>}

arjunmadhusudan
New Contributor II

Re: Year Comparison

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

So, this worked?

arjunmadhusudan
New Contributor II

Re: Year Comparison

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

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

Community Browser