Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Get Maximum of Sum value (set analysis)

Hi Guys,

There are such data:

Date (DD/MM/YYYY)

Place

Region

Value

01/08/2020

1

A

2

01/08/2020

1

B

7

01/08/2020

2

C

4

01/08/2020

2

D

8

01/09/2020

1

A

1

01/09/2020

1

A

9

01/09/2020

1

B

5

01/09/2020

2

C

7

01/09/2020

2

C

3

and I want to present  the Max Sum of Value of the newest date from field "Place" and ignore field "Region" on KPI chart, so the result will be = 15 (1+9+5) (bold).

Can i get that?

I have tried max({1<Place=>}aggr(sum({$<Date ={'$(=Max(Date))'}, Place>} Value), Value)) but it gives me 9.

Thank you

Labels (1)
1 Solution

Accepted Solutions
Creator II
Creator II

You are aggregating by Value, but you need to aggregate on Date to sum all the values. Otherwise it will give you the max value (which is 9).

Try

max({1<Place=>}aggr(sum({$<Date ={'$(=Max(Date))'}, Place>} Value), Date)) 

 

 

View solution in original post

4 Replies
Creator II
Creator II

You are aggregating by Value, but you need to aggregate on Date to sum all the values. Otherwise it will give you the max value (which is 9).

Try

max({1<Place=>}aggr(sum({$<Date ={'$(=Max(Date))'}, Place>} Value), Date)) 

 

 

View solution in original post

MVP
MVP

Are you looking to get data for specific Place,  If you take a max of Date, that date is available for other Place as well. What is the final  output you need?

Contributor II
Contributor II

I just realize that aggr concept, so i modified "Date" into "Place"

so i think it counted as solution 🙂

Contributor II
Contributor II

I am facing another problem 😞

I transform that data into pivot table, so Region "D" has null value.

when i click one by one all of the Region (except "D"), the KPI will stay 15

but if  i click Region "D", it will return 12 (4+8) (Max of Sum of last period)

How can this happen?