Using aggr function in qlik view

[#1]

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

A

20/01/15

04,05

Bangalore , Mumbai

B

30/01/15

4

chennai,delhi

C

20/01/15

2

Ludhiana

E

21/01/15

4

Malayasia

Please suggest .

[swuehl]

Maybe like this?

[#3]

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 .

[Gysbert_Wassenaar]

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.

[swuehl · Accepted solution]

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.

[#6]

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)

[#7]

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 ?

[swuehl]

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

[swuehl]

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.

[#10]

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 .