Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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,
try this
=monthname(aggr(Only({<Year={"$(=max(Year))"}>}PaymentDate),PaymentDate))
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
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)
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.