topic Re: Subtract value from first occurence of data in QlikView

Subtract value from first occurence of data

jamestaylor52 — Wed, 04 Jul 2018 16:08:32 GMT

Hello,

I am working with a graph of Order Date (x axis) and cumulative order value (y axis). This is for a monthly recurring subscription service. I need to subtract a cost (the CPA) from the first instance of the customer ID appearing in the data.

My current method subtracts the CPA from every instance of the customer ID, rather than just the first instance. There should only be 1 CPA per customer, so sum(CPA) = [CPA]* Distinct ( Count ( customer_id ) )

This is my current expression:

rangesum ( above ( ( sum ( [OrderValue]) - ( [CPA]* Distinct ( Count ( customer_id ) ) ) ) ,0,rowno()))

The X-axis is OrderDate, which for a subscription service means the customer ID appears multiple times (as there is 1 order every month).

For example, if CPA = 5

OrderDate	OrderValue	Actual Order Value (the one I want to display)
01/01/2018	20	15
04/02/2018	20	20
03/03/2018	20	20
05/04/2018	20	20

etc

Weirdly, we don't have order number in the data, so I can't simply use {$<[OrderNumber]={'1'}>}

Kind regards,

James

Re: Subtract value from first occurence of data

olivierrobin — Thu, 05 Jul 2018 07:13:39 GMT

hello

and what about using firstsortedvalue() to retrieve the 1st value based on date ?

Re: Subtract value from first occurence of data

jamestaylor52 — Thu, 05 Jul 2018 09:26:35 GMT

Thanks Olivier.

As my graph charts by month, when I've tried firstsortedvalue() it has performed it on every month. So it leaves me as the same result as Distinct ( Count ( customer_id ) ).

Re: Subtract value from first occurence of data

jamestaylor52 — Thu, 05 Jul 2018 11:09:02 GMT

To update, I believe I've worked it out using aggr() function.

(count(aggr(min([order_date.autoCalendar.YearMonth]),customerID) returns a value of 1 at the first month the customer ID appears. This works perfectly!

James