topic Re: Trick in QlikView

Trick

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Can someone please explain me what this below expression is doing ?

=If

(

IsNull(MIN({Filter} Date)) = 0,

Only({<yearMonthSort = {"$(=Date(AddMonths(Today(), -1), 'YYYYMM'))"}>}Sales_StartDate)

)

Re: Trick

Anil_Babu_Samineni — Wed, 05 Apr 2017 08:42:34 GMT

I am not the good explain person, But i will try

Here how this work is - Need to fetch date where null value become as Zero then we need to ignore for state over here and then Fetch minimum date of those null dates and then fetch only Sales_StartDate where the condition works which as YearMonthSort filter is last month of date should get and which format should and applicable as YYYYMM

Finally, It will return as Sales_StartDate where last month and where the Date field has null values for filter

Re: Trick

smilingjohn — Wed, 05 Apr 2017 09:15:23 GMT

Thanks Anil

and what does this means ?

From_Date>=Date(MakeDate(Year(AddYears(Today(),-2)),Num(Month(Today()))),'YYYY-MM-DD'); ?

what is -2 for ? here

Re: Trick

vinieme12 — Wed, 05 Apr 2017 09:23:59 GMT

=If

(

IsNull(MIN({Filter} Date)) = 0, /// if Min(Date) with given {filters} is NOT null()

, // Then

Only({<yearMonthSort = {"$(=Date(AddMonths(Today(), -1), 'YYYYMM'))"}>}Sales_StartDate)

)

// Get Sales_StartDate for Previous month

But using only() means you are trying to retrieve only a single value? or do you want to use Min() to get first Sales_StartDate for previous month ?

Re: Trick

Miguel_Angel_Baeyens — Wed, 05 Apr 2017 09:27:59 GMT

The -2 is a parameter to the AddYears() function, which subtracts 2 years to the current year as specified by Today(), so the whole expression reads:

"Values in From_Date field greater than or equal to 2015-04-01"

Re: Trick

Anil_Babu_Samineni — Wed, 05 Apr 2017 09:29:08 GMT

Here,

Year(AddYears(Today(),-2))

This part will return -- 2015(Because, From Today's year we want to show last 2 year data that means 2017 - 2 = 2015)

Num(Month(Today())))

This part will return in Number format of Today's month

Finally, Makedate will return YYYY, MM, DD Format which means 2015, 04, 01 (Here Makedate consider the first day in default)

Date(MakeDate(Year(AddYears(Today(),-2)),Num(Month(Today()))),'YYYY-MM-DD')

And, Finally this will return and change it as 2015-04-01 due to Date function.

And, I highly recommend you to split and start from your end by help of Text object you will get to know.

Helps to you.

Re: Trick

Miguel_Angel_Baeyens — Wed, 05 Apr 2017 09:29:48 GMT

Only() will return one single value if there is only one single value. Otherwise will return null.

Re: Trick

vinieme12 — Wed, 05 Apr 2017 09:47:34 GMT

Yep, which is why I suggested using Min() instead