Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Contributor III

Logic help

Hi All,

When we calculate the case total at the same grain it works fine.

If we calculate case total at case reference level it does not working, this is because of all case secure are added together(case total = case secure + case settled)

For total at case reference level we need to take the latest case secure and add it to the sum of all the paid amount for that case reference.

Please find attached spread sheet, and can anyone suggest how to achieve/calculate 'Total for the case (latest case secure + total paid)'

 

Thanks,

Ganesh

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Logic help

Well, try like:

Dimension: Reference

Rangesum(Firstsortedvalue(Secure, -Date), sum(TotalPaid))

View solution in original post

5 Replies
Highlighted
MVP
MVP

Re: Logic help

Do you have a field/logic to define which transaction is latest ?

Highlighted
Contributor III

Re: Logic help

Sorry, I have Date field to define the latest transaction,

I have tried the below expression, and it is working only if we select any case references.

aggr(sum({<Date={"$(=Max(Date))"} >}Secure),Reference)+sum(TotalPaid).


please suggest


Thanks,

Ganesh

Highlighted
MVP
MVP

Re: Logic help

If you don't have a time field to decide the recentness and the load order defines the same, you could create a new field like:

Capture2.PNG

and then at UI use firstsortedvalue() like:

Capture.PNG

Highlighted
MVP
MVP

Re: Logic help

Well, try like:

Dimension: Reference

Rangesum(Firstsortedvalue(Secure, -Date), sum(TotalPaid))

View solution in original post

Highlighted

Re: Logic help

As set analysis works once per chart, this can't be done using set analysis, use below

sum(aggr(if(Date =max(total <Reference> Date),Secure),Reference))+sum(TotalPaid).