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: 
Anonymous
Not applicable

FirstSortedValue with dates and aggr

Dear all, I have been battling with this problem for many days now and would like to ask for some help on how to solve.

I have a table of data which contains transactions and mappings to different product types and I am trying to create a table that for each client entry and for each product type:

1. What is the first date of investment (where amount >0)

2. What is the amount in the first date of investment

I started off trying to create a set analysis to show the amount on a particular date using a variable for the min month but it got too complex so went down the first value route.

In the attached file I share the data table - I am only interested in Aspect = R and for aggregation of the values at Prod_Cotegory_Desc

The table I am trying to create would end up looking like this:

ClientProduct CategoryFirst  Month of InvestmentInvestment Amount
Client 1DJan-2017129
Client 1EAug-20173,663
Client 1PEJan-20173,361
Client 2CL&CJan-201725,353
Client 2DJan-201797

One of the things I noticed with the data was that Qlik was not handling the date field sequentially so I created the FACT_MONTH_YEARDATE_NQA field on the date value so sorting was more simple.

The furthest I got was creating a formula something like this:

First Investment Date:

AGGR(

FirstSortedValue(

     AGGR(MIN({$<AMOUNT = {'>0'}>}FACT_MONTHYEAR_DATE),RELN_NBR,PROD_COTEGORY_DESC), //Mind date per prod    

          AGGR(MIN(FACT_MONTHYEAR_DATE),RELN_NBR,PROD_COTEGORY_DESC) //sort by date and product

     )

,RELN_NBR,PROD_COTEGORY_DESC)  //aggregate at product level

This appears to produce the correct date value.

Amount for first investment date:

AGGR(

FirstSortedValue(

     AGGR(SUM({$<AMOUNT = {'>0'},ASPECT = {'R'}>}AMOUNT),RELN_NBR,PROD_COTEGORY_DESC), //Min amount per prod

          AGGR(MIN(FACT_MONTHYEAR_DATE),RELN_NBR,PROD_COTEGORY_DESC) //sort by date and product

)

,RELN_NBR,PROD_COTEGORY_DESC)  //aggregate at product level

the result of the above is that it sums the R factor and does not show the minimum value. This minimum value would be the sum of all the values in the minimum month where aspect = R

Any help much appreciated!

1 Solution

Accepted Solutions
sunny_talwar

Try these

First Investment Date

Min(Aggr(If(Sum(AMOUNT) <> 0, FACT_MONTHYEAR_DATE_NQA), RELN_NBR, PROD_COTEGORY_DESC, FACT_MONTHYEAR_DATE_NQA), 1)

FirstValue

FirstSortedValue(Aggr(If(Sum(AMOUNT) <> 0, Sum(AMOUNT)), RELN_NBR, PROD_COTEGORY_DESC, FACT_MONTHYEAR_DATE_NQA), Aggr(If(Sum(AMOUNT) <> 0, FACT_MONTHYEAR_DATE_NQA), RELN_NBR, PROD_COTEGORY_DESC, FACT_MONTHYEAR_DATE_NQA), 1)

Capture.PNG

View solution in original post

7 Replies
sunny_talwar

Try these

First Investment Date

Min(Aggr(If(Sum(AMOUNT) <> 0, FACT_MONTHYEAR_DATE_NQA), RELN_NBR, PROD_COTEGORY_DESC, FACT_MONTHYEAR_DATE_NQA), 1)

FirstValue

FirstSortedValue(Aggr(If(Sum(AMOUNT) <> 0, Sum(AMOUNT)), RELN_NBR, PROD_COTEGORY_DESC, FACT_MONTHYEAR_DATE_NQA), Aggr(If(Sum(AMOUNT) <> 0, FACT_MONTHYEAR_DATE_NQA), RELN_NBR, PROD_COTEGORY_DESC, FACT_MONTHYEAR_DATE_NQA), 1)

Capture.PNG

Anonymous
Not applicable
Author

Sunny - you are my hero! Works like a charm - thank-you so much!!

madhuparnadhar
Contributor III
Contributor III

Hi Sunny,

I was stuck with a similar problem and your solution worked for me as well. Will you please help me in understanding how you got the first investment date ?

Thanks and Regards,

Madhuparna Dhar

sunny_talwar


@madhuparnadhar wrote:

Hi Sunny,

Will you please help me in understanding how you got the first investment date ?


Sure, but what exactly are you not clear about in the expression?

madhuparnadhar
Contributor III
Contributor III

Hi Sunny,

I thought Aggr must have an aggregate function inside it to work. The IF statement inside Aggr returns the dates with count greater than 0 and I am wondering how the Aggr works with that.

I have one more query : I have transactions for four stores with individual first dates. I want to use this date in set analysis and get all the orders for dates greater than this first date. But I can't make it work right now. The expression that I am using currently is as below :

Count({< [Date]={">=$(=Date(Min(Aggr(If(Sum([Visitors Count]) <> 0, Date), Date,[Store Name]))))"} >} Distinct [Order Number])

Could you please help me in figuring this out ?

Thanks and Regards,

Madhuparna Dhar

sunny_talwar


@madhuparnadhar wrote:

I thought Aggr must have an aggregate function inside it to work. The IF statement inside Aggr returns the dates with count greater than 0 and I am wondering how the Aggr works with that.


Not necessarily. If you have a single value based on the different dimensions you use in Aggr(), then you can use it without function. When we say without function there is a only() function which is optional and can be removed. What Only() means is that you have one and only one value for the list of the dimension.


@madhuparnadhar wrote:

I have one more query : I have transactions for four stores with individual first dates. I want to use this date in set analysis and get all the orders for dates greater than this first date. But I can't make it work right now.


It would be rather difficult to solve a problem like this without a sample or sample data to look at. May be if you are able to share a sample with the output you expect to see from it, I might be able to help you out.

madhuparnadhar
Contributor III
Contributor III

Hi Sunny,

Let me try to explain this with the following table :

DateStore Name# OrdersVisitors Count
01-01-2019S150
01-01-2019S250
02-01-2019S150
02-01-2019S2510
03-01-2019S1510
03-01-2019S2510


The above table has transactions for two stores. As you can see, the order and visitor details are not available from same dates. The order details are present from 01-01-2019 whereas the visitor details are present from different dates for each store. I want to compare number of orders against number of visitors for each store. To get correct numbers, I need to find out number of orders starting with dates when both order and visitor details are present (and not from 01-01-2019).

The expression Date(Min(Aggr(If(Sum([Visitors Count]) <> 0, Date), Date,[Store Name]))) gives me the minimum date from which visitor details are present for each store. Now I want to find out number of orders for dates greater than equal to this date. 

For example, store S2 has visitor details from 02-01-2019, so for store S2 total number of orders should be 10 (considering 02-01-2019, since when both order and visitor details are present), instead of 15 (if date 01-01-2019 was considered).

Hopefully I can make some sense here. Please feel free to let me know in case you need some more info.

Thanks and Regards,

Madhuparna Dhar