Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

benjamin_peter_
New Contributor

Sum of amount when date is minimum

Hello community,

I have already learned a lot from all of your earlier posts and answers but for my question today I got stuck and would like to ask for your help.

I have a table of historical customer data. The table includes the colums ID, Status, Date, and Amount. Each customer can have several rows. In my analysis I need the sum of the amount per customer of their first entry in the status 115.

As an example:

 

RowIDStatusDateAmount
110011501.01.201710000
210011501.01.201810000
310011501.01.20165000
420011001.06.20158000
520011501.07.20158000
620011501.08.20158000
722211001.01.201710000
822211201.01.201810000
922211501.01.20165000

The relevant rows that have to be taken into the calculation would be the rows 3, 5 and 9 because for customer 100 the first entry in the status 115 would be in January 2016. For customer 200 it would be July 2015 and for customer 222 it would be January 2016 again.

So if I would take the sum for January 2016 the result would have to be 10.000.

I have already tried several ideas from the forum. One solution that gives me the correct count (in this example=2) is the following formula: count({$<STATUS={115}>} aggr(min ([Date]),  [ID] )) but I can't figure out how to change the count into sum.

One other idea was to go with a calculated field and set a flag in the relevant rows (3,5,9). But i didnt find a way to compare the rows regarding minimum value.

So I'm counting on you :-) Do you have any idea how to solve this challenge?

1 Reply

Re: Sum of amount when date is minimum

May be try this

FirstSortedValue({<Status = {'115'}>} Aggr(Sum({<Status = {'115'}>}Amount), ID, Date), Aggr(Only({<Status = {'115'}>}Date), ID, Date))

Community Browser