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

[Kushal_Chawda]

try this

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

[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]

Using the above code only give Jan 2020.
I made small change in the code you gave  replaced Max(PaymentDate) with Today() , however i get Data of both 2019 and 2020.

I know i need to do very little change to get only 2019 data i am working on it.

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

to get only 2019 data i tried the below code , but no luck:-

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

[Kushal_Chawda · Accepted solution]

may be this

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

[qliksus]

One more simple way of do it is 

aggr( Only({<Payementdate={"=year(Payementdate)=year(today())"}>} Payementdate) ,Payementdate)

[kalyanamharish]

@qliksus  Yes, less complex code.

What if we need to restrict the data to Current month (i,e only December of 2019) using your code?

 
I tried this , but this gives me December data for other years as well like (Dec 2016, Dec 2017, Dec 2018, Dec 2020)

=aggr( Only({<PaymentDate={"=year(PaymentDate)=year(today())" , "=month(PaymentDate)=month(today())"} >} MonthName(PaymentDate)) ,PaymentDate)

[veidlburkhard]

Hi Kalyanamharish,

why don't you make ist more simple and create this new field in your script:

T1:
LOAD
PaymentDate,
Date(Date#(PaymentDate, 'MM/DD/YYYY'), 'MMM YYYY') as PaymentDateNew,
Year(Date#(PaymentDate, 'MM/DD/YYYY')) as PaymentYear;
LOAD * INLINE [
PaymentDate
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
];

In front end it looks like this:

Your listbox will show the paymentDates of your selected year, only.

Hope this helps

Burkhard

[qliksus]

Maybe something like the below 

aggr( Only({<Payementdate={"=year(Payementdate)&month(Payementdate)=year(today())&month(today())"}>} Payementdate) ,Payementdate)