topic Re: Usin max value in a linechart in QlikView

Usin max value in a linechart

Dwyane — Wed, 18 Aug 2021 14:31:23 GMT

Hello all,

I have a table like this

Month	Customer Name	Paid	Collected	Actual
1	John Smith	100	1000	10%
2	John Smith	200	1050	19%
3	John Smith	300	1100	27%
1	Kirk Polly	250	1150	22%
2	Kirk Polly	355	1200	30%
3	Kirk Polly	865	1250	69%

The actual column is calculated by Collected divided by Paid. What i want to do is identify the max month so int examples is 3 for both customers and then use the collected amount for the max month and use that value to divide by the paid for each month. So month 1 for John Smith would be 300/1100 month 2 would be 200/1100 Any idea how i can do this in a line chart where i plot the calculated actual using the month and customer name as dimensions.

Thanks in advance!

Re: Usin max value in a linechart

tincholiver — Thu, 19 Aug 2021 13:34:44 GMT

hi Dwyane,

I hope this is the solution you are looking for: in the dimensions of the graph there is Customer Name and Month, in the expression
1. aggr (NODISTINCT FirstSortedValue (Collected, -Month), [Customer Name]) returns the value of the collection of the last month
2. sum (Paid) sum the payments
3. sum (Paid)
/
aggr (NODISTINCT FirstSortedValue (Collected, -Month), [Customer Name]) is the expression you need

Re: Usin max value in a linechart

tincholiver — Thu, 19 Aug 2021 13:37:05 GMT

If you put the month as a dimension first and then the name of the client you would have something like this:

Re: Usin max value in a linechart

tincholiver — Thu, 19 Aug 2021 13:47:42 GMT

Sorry, you were looking to graph the data in lines, it would look something like this