topic Problem with AGGR and MAX Value on a DateRange in QlikView

Problem with AGGR and MAX Value on a DateRange

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I got some problem to solve my expression, i tried a ton of solution but none worked...

I have this table :

Data:

LOAD * INLINE [

idData,codedocData, contributionData, DateData

1, 1, 1, 01/02/2015

2, 1, 11, 01/02/2015

3, 1, 12, 01/03/2015

4, 1, 19, 05/03/2015

5, 1, 15, 15/03/2015

6, 2, 1, 03/03/2015

7, 2, 11, 03/03/2015

8, 2, 12, 03/03/2015

9, 2, 19, 07/03/2015

10, 2, 15, 20/03/2015

11, 1, 59, 21/03/2015

12, 1, 15, 30/03/2015

];

I want to find between a range of date (StartDate, EndDate) the value (contributiondata) of the max idData by codedoc.

See the attached qvw.

Thanks if someone can help me !

Re: Problem with AGGR and MAX Value on a DateRange

kaushiknsolanki — Mon, 01 Aug 2016 10:16:48 GMT

Hi,

Hope this is what you want.

Just FYI Just put the date as it is in Inputbox, no need to use date function for that.

Regards,

Kaushik Solanki

Re: Problem with AGGR and MAX Value on a DateRange

Anil_Babu_Samineni — Mon, 01 Aug 2016 10:20:03 GMT

Why are you taking this?

Date('01/03/2015')

Re: Problem with AGGR and MAX Value on a DateRange

Mon, 01 Aug 2016 10:32:23 GMT

Thanks man !

Could you please explain this expression :

Sum( {<idData = {$(=Concat(total<codedocData> aggr( MAX({<DateData = {'>=$(vStart)<=$(vFrom)'} >}idData),codedocData),','))}>}contributionData)

for a new Qlik user?

Best regards

Re: Problem with AGGR and MAX Value on a DateRange

Mon, 01 Aug 2016 10:34:23 GMT

I took this to test my range date with a begining and a end.

Re: Problem with AGGR and MAX Value on a DateRange

sunny_talwar — Mon, 01 Aug 2016 10:36:41 GMT

Is this what you want to get?

I used this expression:

Max({< DateData = {"$(='>=' & vStart & '<=' & vFrom)"} >} idData)

2 changes I did was to remove Aggr() function and the other one was to change the set analysis around. I removed Aggr(), because I didn't think it was required here. For set analysis, I think the best way is to check what you get when you put it in a text box object.

You had this: ='>=' & Date($(vStart)) & '<=' & Date($(vFrom))

Where as I played around with it by first removing the Date() function and then the dollar sign expansion to get this: ='>=' & vStart & '<=' & vFrom

Once you got the right date range in the right date field format, all you need is to take the text box expression and put it within the dollar sign expansion:

Max({< DateData = {"$(='>=' & vStart & '<=' & vFrom)"} >} idData)

HTH

Best,

Sunny

Re: Problem with AGGR and MAX Value on a DateRange

kaushiknsolanki — Mon, 01 Aug 2016 11:03:51 GMT

Hi,

When you use the Max function in Set analysis, it will give you Max for overall set and not for individual dimension value, so what i did is I took the Max at Individual level and concatenated them, so that I can pass them in set analysis and get the required result.

$(=Concat(total<codedocData> aggr( MAX({<DateData = {'>=$(vStart)<=$(vFrom)'} >}idData),codedocData),','))

This function will do that.