MMMYYYY comparison

[arjunmadhusudan]

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)

[swarnendu]

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]

Hi,

can you share sample app or data ?

[arjunmadhusudan]

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)

MMMYYYY comparison

[YoussefBelloum]

your filter Year is related to which time field ?

[arjunmadhusudan]

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]

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