To receive only the max value for several data records for one month

[#1]

Hi,

im really a freshman in matters of working with the development tool of qlikview.

Actually I have the concrete problem that I have serval data records each for different days in one month and that over years.

What I want is, that I get only the (one) peak values of the data record for one month.

What is the solution and how is the formula scripted in qlikview for this query?

Thanks for your support!

Greetings Greg

[tamilarasu]

Hi Gregor,

You can use max function or FirstSortedValue according to your requirement. I have showed two examples for better understanding!!.

Highest sales value for each month:

Data:
LOAD * INLINE [
    Month, Sales
    Jan, 100
    Jan, 200
    Jan, 300
    Feb, 400
    Feb, 500
    Feb, 600
    Mar, 700
    Mar, 800
    Mar, 900
];

NoConcatenate
LOAD Month,Max(Sales) as Sales
Resident Data
Group by Month;

DROP Table Data;

Output:

  Highest sales value and the person name for each month.

LOAD * INLINE [
    Month, Person, Sales
    Jan, John, 100
    Jan, John, 200
    Jan, John, 300
    Jan, Gregor, 500
    Jan, Gregor, 600
    Jan, Gregor, 700
    Feb, John, 800
    Feb, John, 900
    Feb, John, 1000
    Feb, Gregor, 1100
    Feb, Gregor, 1200
    Feb, Gregor, 1300
    ];

NoConcatenate
LOAD Month,
FirstSortedValue(Person, -Sales) as Person,
FirstSortedValue(Sales, -Sales) as Sales
Resident Data
Group by Month;

Drop Table Data;
DROP Table Data;

Output:

If this is not what you want, please post your sample input file and expected output.

[#3]

Hi Tamil,

thank you for your quick answer. I will try your solution tomorrow at office. If it dont work, i will come back for an question to you.

Have a nice sunday.

Greetings Greg

[#4]

Temp:

Load *,

     Year(Date) as Year,

     Month(Date) as Month,

     Day(Date) as Day;

Load * Inline [

Date

04/20/2016

04/21/2016

04/29/2015

03/11/2016

03/19/2016

03/29/2016

];

Final:

Load date(Max(Date),'MM/DD/YYYY') as MaxDate

Resident Temp

group by Year, Month;

Drop table Temp;

[#5]

Thanks for your help!