Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kalyanamharish
Contributor III
Contributor III

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

Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

may be this

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

View solution in original post

8 Replies
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
Contributor III
Contributor III
Author

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

may be this

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

qliksus
Specialist II
Specialist II

One more simple way of do it is 

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

 

 

kalyanamharish
Contributor III
Contributor III
Author

@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
Creator III
Creator III

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:

DateFormat.jpg

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

Hope this helps

Burkhard

qliksus
Specialist II
Specialist II

Maybe something like the below 

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