Qlik Community

Qlik Sense App Development

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

arjunmadhusudan
New Contributor II

MMMYYYY comparison

I have two filters  Year  and MonthYear (MMMYYYY format).

User can select only year or year and MonthYear both or just MonthYear

CreatedMonthYears and CloseMonthYear are also in MMMYYYY format and  MonthYears is also in MMMYYYY format.

Year comparison shown below works well for me but MonthYear comparison which is in MMMYYYY format does not work. .

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)

6 Replies
swarnendu
New Contributor II

Re: MMMYYYY comparison

Please check this

Why don’t my dates work?

Get the Dates Right

QlikView Date fields

check out this for dates in set analysis

Dates in Set Analysis

May be its can help you.

YoussefBelloum
Esteemed Contributor

Re: MMMYYYY comparison

Hi,

can you share sample app or data ?

arjunmadhusudan
New Contributor II

Re: MMMYYYY comparison

Outletid   CreatedYear  CreatedMonthYear  ClosedYear ClosedMonthYear

15253        2017              Mar2017                2018                  Feb2018

23123        2016              Dec2016                2017                  Nov2017

23122        2015              Jan2015                2017                   Aug2017

Filters

Year                         MonthYear

2017                        Jan2017 to....... Mar2018

2018

If I select only Year = 2017  the count of outletid  = 1,  as only one outletid 15253 is opening before  max 2017 ie. Dec2017 and closing after dec2017.

If i select MonthYear = Jan2018 the count should be = 1 as only one outlet is opening before but closing after Jan2018 which is outletid = 15253

Please referr below expression  I am using currently which is not working for me.

Count({

$<

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

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

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

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

} Distinct Outletid)

YoussefBelloum
Esteemed Contributor

Re: MMMYYYY comparison

your filter Year is related to which time field ?

arjunmadhusudan
New Contributor II

Re: MMMYYYY comparison

It is currently not linked to any field but I am trying to compare it to CreatedYear.

Year and MonthYear is in the same table.

YoussefBelloum
Esteemed Contributor

Re: MMMYYYY comparison

EDITED:

I think the problem here is related to the Max() function associated to a non numeric value..

try to create derived Month_year field, using this format: MMYYYY to be able to use the max().

you can use this to convert JAN to 01 for example:

=num(month(date#(Month_label_field,'MMM')),'00')


using your Month_year field, one way would be this:


=Date#(num(month(date#(left(Month_Year,3),'MMM')),'00')&right(Month_Year,4),'MMYYYY') as Month_Year_2


Community Browser