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

[kalyanamharish]

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/...

Thanks

Harish

[Taoufiq_Zarra · Accepted solution]

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,

[Kushal_Chawda]

try this

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

[Kushal_Chawda]

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

[kalyanamharish]

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)

[Taoufiq_Zarra]

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.