Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kalyanamharish
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

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 :

Capture.PNG

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,

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

5 Replies
Taoufiq_Zarra
Master II
Master II

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 :

Capture.PNG

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,

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Kush
MVP
MVP

try this

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

Kush
MVP
MVP

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
Contributor III
Contributor III

 

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
Master II
Master II

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.

 

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉