topic Re: List box without any aggregation in QlikView

List box without any aggregation

kalyanamharish — Sat, 16 Nov 2024 01:37:59 GMT

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

Thanks

Harish

Re: List box without any aggregation

Kushal_Chawda — Wed, 25 Dec 2019 15:50:14 GMT

try this

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

Re: List box without any aggregation

Kushal_Chawda — Wed, 25 Dec 2019 16:03:58 GMT

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

Re: List box without any aggregation

kalyanamharish — Wed, 25 Dec 2019 16:15:23 GMT

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))

Re: List box without any aggregation

Kushal_Chawda — Wed, 25 Dec 2019 16:22:01 GMT

may be this

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

Re: List box without any aggregation

qliksus — Thu, 26 Dec 2019 03:44:17 GMT

One more simple way of do it is

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

Re: List box without any aggregation

kalyanamharish — Thu, 26 Dec 2019 10:26:44 GMT

@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)

Re: List box without any aggregation

veidlburkhard — Thu, 26 Dec 2019 11:37:41 GMT

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

Re: List box without any aggregation

qliksus — Fri, 27 Dec 2019 04:43:21 GMT

Maybe something like the below

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