topic MMMYYYY comparison in App Development

MMMYYYY comparison

arjunmadhusudan — Mon, 09 Apr 2018 08:30:17 GMT

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)

Re: MMMYYYY comparison

swarnendu — Mon, 09 Apr 2018 08:34:59 GMT

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.

Re: MMMYYYY comparison

YoussefBelloum — Mon, 09 Apr 2018 08:43:35 GMT

Hi,

can you share sample app or data ?

Re: MMMYYYY comparison

arjunmadhusudan — Mon, 09 Apr 2018 14:09:34 GMT

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

Re: MMMYYYY comparison

YoussefBelloum — Mon, 09 Apr 2018 14:22:17 GMT

your filter Year is related to which time field ?

Re: MMMYYYY comparison

arjunmadhusudan — Mon, 09 Apr 2018 14:39:59 GMT

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.

Re: MMMYYYY comparison

YoussefBelloum — Mon, 09 Apr 2018 14:50:28 GMT

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