topic Re: FirstSortedValue with dates and aggr in QlikView

FirstSortedValue with dates and aggr

Anonymous — Tue, 12 Dec 2017 16:19:46 GMT

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:

Client	Product Category	First Month of Investment	Investment Amount
Client 1	D	Jan-2017	129
Client 1	E	Aug-2017	3,663
Client 1	PE	Jan-2017	3,361
Client 2	CL&C	Jan-2017	25,353
Client 2	D	Jan-2017	97

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!

Re: FirstSortedValue with dates and aggr

sunny_talwar — Tue, 12 Dec 2017 17:56:02 GMT

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)

Re: FirstSortedValue with dates and aggr

Anonymous — Tue, 12 Dec 2017 18:02:38 GMT

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

Re: FirstSortedValue with dates and aggr

madhuparnadhar — Fri, 08 Mar 2019 14:37:45 GMT

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

Re: FirstSortedValue with dates and aggr

sunny_talwar — Fri, 08 Mar 2019 14:56:02 GMT

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

Re: FirstSortedValue with dates and aggr

madhuparnadhar — Fri, 08 Mar 2019 15:53:17 GMT

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

Re: FirstSortedValue with dates and aggr

sunny_talwar — Fri, 08 Mar 2019 16:21:11 GMT

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

Re: FirstSortedValue with dates and aggr

madhuparnadhar — Sat, 09 Mar 2019 00:06:53 GMT

Hi Sunny,

Let me try to explain this with the following table :

Date	Store Name	# Orders	Visitors Count
01-01-2019	S1	5	0
01-01-2019	S2	5	0
02-01-2019	S1	5	0
02-01-2019	S2	5	10
03-01-2019	S1	5	10
03-01-2019	S2	5	10

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