Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Find highest Month and Value

Dear All,

I have the following example data set:

YearCaseMonthValue
201339019112
201339019122
201339019133
201339019143
201339019154
201339019164
201339019175
201339019186
201343400212
201343400224
201343400231

I want to create a table per year, that sums the values of the highest available month per Case.

In this example for case 390191 the highest month is 8 with value 6 and case 434002 has highest month 3 with value 1.

So the result should be:

YearSum(value)
20137

How do I achief this??

Thank you for your help!!

Frank

1 Solution

Accepted Solutions

Try with firstsortedvalue

=Sum(Aggr(FirstSortedValue(Value, -Month) ,Case))

View solution in original post

5 Replies
Specialist III
Specialist III

Frank,

just taken a look at this and

1 - I've changed your load script to concatenate month and case into a new field MonthCase

LOAD * INLINE [
    Year, Case, Month, Value
    2013, 390191, 1, 2
    2013, 390191, 2, 2
    2013, 390191, 3, 3
    2013, 390191, 4, 3
    2013, 390191, 5, 4
    2013, 390191, 6, 4
    2013, 390191, 7, 5
    2013, 390191, 8, 6
    2013, 434002, 1, 2
    2013, 434002, 2, 4
    2013, 434002, 3, 1
]
;

Data:
LOAD *,
Num(Text(Month) & Text(Case)) as MonthCase
RESIDENT TmpData;

DROP TABLE TmpData;

Then, I can display a table showing your Year dimension and this expression:

Sum({$<MonthCase={$(=Concat(Aggr(Max(MonthCase),Year,Case),','))}>} Value)


Hope this helps,

Marcus

Try with firstsortedvalue

=Sum(Aggr(FirstSortedValue(Value, -Month) ,Case))

View solution in original post

Not applicable

Hi Frank,

To do what you describe, I think you should do it in 3 steps:

1 - In the script, load in a temporary table the year, the case and the max month like this :

TEMP:

LOAD

    Year,

    Case,

    Max(Month) as Month

RESIDENT

    DATA

GROUP BY

    Year, Case;

2 - Always in the script, join the datas from the temporary table and a flag with your data table and drop the temporayr table like this :

Left Join (DATA)

LOAD

    Year,

    Case,

    Month,

    1 as [Flag max month]

RESIDENT

    TEMP;

DROP TABLE TEMP;

3 - The to sum the values of the highest available month per Case, use this expression :

=Sum({$<[Flag max month] = {1}>} Value)

I attach an application with this example.

Hope it helps.

Regards,

Nicolas

Not applicable

Hi

please find the attached file . hope will it helps you.

Thanks & Regards,

Amuthabharathi.

Partner
Partner

That was what I needed! Thank you very much! 🙂