topic Re: Using Year and MonthName function in Set analysis inside List box without any aggregation. in App Development

Using Year and MonthName function in Set analysis inside List box without any aggregation.

kalyanamharish — Sat, 16 Nov 2024 19:23:43 GMT

Hi,

=if(year(PaymentDate)='2019', Monthname(PaymentDate))

I am using above code in a List box . The output of the list box is:-

Jan 2019

Feb 2019

Mar 2019

Apr 2019

May 2019

Jun 2019

Jul 2019

Aug 2019

Sep 2019

Oct 2019

Raw data present in PaymentDate field as follows: -

01/31/2019

02/28/2019

03/31/2019

04/30/2019

05/31/2019

06/30/2019

07/31/2019

08/31/2019

09/30/2019

10/31/2019

01/31/2020

My need, I want to write the same code using Set Expression / Set Analysis and instead of hardcoding the year 2019 i want to make it Dynamic.

My efforts so far :

Try 1:

=Only({<Year(PaymentDate = {"$(=Year(Today()))"})>} MonthName(PaymentDate))

Try 2:

= Aggr(Only({<Year(PaymentDate = {"$(=Year(Today()))"})>} MonthName(PaymentDate)) , MonthName(PaymentDate))

I took reference from https://community.qlik.com/t5/QlikView-Creating-Analytics/Set-analysis-without-any-aggregation/td-p/1004149

Thanks

Harish

Re: Using Year and MonthName function in Set analysis inside List box without any aggregation.

Taoufiq_Zarra — Wed, 25 Dec 2019 15:48:17 GMT

Hi,

Maybe this :

=Aggr(Only({<PaymentDate={">=$(=makedate(Year(today()),1,1)) <=$(=makedate(Year(today()),12,31))"}> } MonthName(PaymentDate)), PaymentDate)

the script is independent of '2019', it looks for the max of the date

the output :

you just have to make sure the date's in the right format.

if you have any hesitation modify your script with this line

Date(Date#(PaymentDate,'MM/DD/YYYY')) as DatePayment,

Re: Using Year and MonthName function in Set analysis inside List box without any aggregation.

Kushal_Chawda — Wed, 25 Dec 2019 15:51:48 GMT

try this

=monthname(aggr(Only({<Year={"$(=max(Year))"}>}PaymentDate),PaymentDate))

Re: Using Year and MonthName function in Set analysis inside List box without any aggregation.

Kushal_Chawda — Wed, 25 Dec 2019 16:03:25 GMT

If you don't have Year field in your model. you can use below expression. Bur I would advice to create Year field in model via calendar

=monthname(aggr(Only({<PaymentDate={">=$(=Date(yearstart(max(PaymentDate)),'MM/DD/YYYY'))"}>}PaymentDate),PaymentDate))

Make sure that PaymentDat format is applied within set analysis are same else set analysis won't work. Other Option is to apply Date format in script itself using Date function

Re: Using Year and MonthName function in Set analysis inside List box without any aggregation.

kalyanamharish — Wed, 25 Dec 2019 18:07:00 GMT

Hey Zarra,

Can you explain this part

{">=$(=makedate(Year(today()),1,1)) <=$(=makedate(Year(today()),12,31))"}

I know that

>=$(=makedate(Year(today()),1,1 ) gives us 2019/01/01

<= $(=makedate(Year(today()),12,31)) gives us 2019/12/31

My understanding is PaymentDate = 2019/01/01 2019/12/31 , how Paymatedate validates the two output.
Is this how we implement Between function in qlik sense/qlik view or this is something else?

I experimented below code. but gave me unintended data

=Aggr(Only({<PaymentDate={">=$(=makedate(Year(today()),1,1)) , <=$(=makedate(Year(today()),12,31))"}> } MonthName(PaymentDate)), PaymentDate)

=Aggr(Only({<PaymentDate={">=$(=makedate(Year(today()),1,1)) and <=$(=makedate(Year(today()),12,31))"}> } MonthName(PaymentDate)), PaymentDate)

=Aggr(Only({<PaymentDate={">=$(=makedate(Year(today()),1,1)) or <=$(=makedate(Year(today()),12,31))"}> } MonthName(PaymentDate)), PaymentDate)

Re: Using Year and MonthName function in Set analysis inside List box without any aggregation.

Taoufiq_Zarra — Wed, 25 Dec 2019 19:36:05 GMT

Hi @kalyanamharish

the script works well for you ?

in the problem statement you look up all months in a list for a given year.

let's assume it's the year 2019

=makedate(Year(today()),1,1 ) gives us 2019/01/01

=makedate(Year(today()),12,31)) gives us 2019/12/31

so,

{">=$(=makedate(Year(today()),1,1)) <=$(=makedate(Year(today()),12,31))"}

in set analysis when we put something between "" means that we search in the field, here [PaymentDate ], the values corresponding to the evaluation between "", i.e. higher than 01/01/2019 and lower 31/12/2019.

so that's exactly what you said.