Problem with AGGR and MAX Value on a DateRange

[#1]

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 !

[kaushiknsolanki · Accepted solution]

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

[Anil_Babu_Samineni]

Why are you taking this?

Date('01/03/2015')

[#4]

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

[#5]

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

[sunny_talwar]

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

[kaushiknsolanki]

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.