topic Re: Using aggr function in qlik view in QlikView

Using aggr function in qlik view

Sun, 02 Aug 2015 11:16:37 GMT

Dear Experts ,

My requirement looks simple ,but am i new to qlik view , struggling with below points . please help me .

I am designing new customer report . i want to show list of customer based on customer booking date .

when the customer should not have any booking for the past 6 months before (from date) and customer available between my selection date , then we consider it as new customer .

In my example my date selection is from 1st jan 2015 - 30 jan 2015 .

i want to show customer in dimension and booking_date , origin , segment in expression as it is liked below sample output .

in my example i have taken customer A,B,C,D,E,F , below is an sample output where customer D and F is not an new customer . customer D booking date is not in between of my selection date and customer F booking date is between of selection date but its available with in 6 months before from date .

i have attached qvd , please guide me .

Customer

Booking Date

Segment

Origin

20/01/15

04,05

Bangalore , Mumbai

30/01/15

chennai,delhi

20/01/15

Ludhiana

21/01/15

Malayasia

Please suggest .

Re: Using aggr function in qlik view

swuehl — Sun, 02 Aug 2015 12:56:40 GMT

Maybe like this?

Re: Using aggr function in qlik view

Mon, 03 Aug 2015 07:01:52 GMT

Dear Swuehl ,

Thanks a lot , its really looks great.

But when i am trying to apply same logic in to my test environment , i am getting booking date and create date as null column.

i think this is happening because of bad date format . please advise .

Re: Using aggr function in qlik view

Gysbert_Wassenaar — Mon, 03 Aug 2015 13:02:44 GMT

See attached qvw. Does that work for you? I reduced the data so my example doesn't use the full data set of the document you posted above.

Re: Using aggr function in qlik view

swuehl — Mon, 03 Aug 2015 18:13:23 GMT

I don't think it's about bad date format, but case sensitivity. QV is case sensitive with regard to field names, and you used Booking_Date instead of Booking_date in your calculated dimension.

I don't think you need the other calculated dimension to show Created Date, an expression with =Only(CCDate) should work.

Attached the corrected version.

P.S. Please try to limit the application size you upload a little bit, it should be possible to demonstrate your issue with a subset of data.

No need to post 2 Million fact records.

Re: Using aggr function in qlik view

Tue, 04 Aug 2015 13:53:19 GMT

Dear Swuehl ,

Noted your message , The way your writing expression looks some what advanced for me .

In my business i am understanding my requirement and even got an logic how to apply in qlikview . But when i start to write set expression , its look some what difficult for me . i can able to understand your expression, but could you please explain , what is the purpose of using total<Customer> here .

=aggr(if(Booking_Date = max({<Booking_Date = {">=$(=num(vFromDate))<=$(=num(vToDate))"}>} total<Customer> Booking_Date) and not (min({<Booking_Date = {"<=$(=num(vFromDate))"}>}total<Customer> Booking_Date)>=addmonths(num(vFromDate),-6))=true(),Booking_Date),Customer,Booking_Date)

Re: Using aggr function in qlik view

Tue, 04 Aug 2015 14:00:26 GMT

Dear Gysbert ,

Thanks for your reply , Usually if i want to reduce data i will hardcore some value to check .

Could you please explain how u reduced data without any hardcore value , is there any settings to reduce data ?

Re: Using aggr function in qlik view

swuehl — Tue, 04 Aug 2015 14:07:33 GMT

The advanced aggregation aggr() function uses two dimensions, Customer and Booking_date, so any aggregation will be grouped by combination of these field values. If we want to find the min() Booking_date per Customer only, (in a given date range set using set analysis), we need to use the TOTAL qualifier with a field list for Customer to do an aggregation not considering the Booking_date dimension, but only Customer dimension.

Hope this helps,

Stefan

Re: Using aggr function in qlik view

swuehl — Tue, 04 Aug 2015 16:08:25 GMT

You can reduce the data in QV Desktop by making an appropriate selection, then go to File ->Reduce Data -> Keep possible Values. This removes all excluded from your data model until next reload.

Re: Using aggr function in qlik view

Sat, 08 Aug 2015 10:39:33 GMT

Dear Swuehl ,

Due to huge data , its taking more time to load for each and every selection . Instead of taking Booking Date in dimension , is it possible to write same in Expression . i think calculated dimension taking more time to respond ,

Keeping customer alone in dimension , rest of fields will use in expression will solve.

Please suggest .

Re: Using aggr function in qlik view

Fri, 21 Aug 2015 11:18:51 GMT

Dear Gysbert ,

Thanks a lot for your help .

Could you please explain me how this expression is working , it will exclude all the customer based on condition ?

=date(aggr(

Max({<

Booking_date={">=$(vFromDate)<=$(vToDate)"},

Customer=e({<Booking_date={">=$(=num(addmonths($(vFromDate),-6)))<$(vFromDate)"}>}Customer)

>} Booking_date)

,Customer),'DD-MMM-YYYY')

Re: Using aggr function in qlik view

Gysbert_Wassenaar — Fri, 21 Aug 2015 14:42:27 GMT

Customer=e({<Booking_date={">=$(=num(addmonths($(vFromDate),-6)))<$(vFromDate)"}>}Customer)

The e() function is to selected excluded values. In this case select those customers that do not have a record with a booking date in the six months preceding the vFromDate date.