Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
jamestaylor52
New Contributor II

Subtract value from first occurence of data

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
OrderValueActual Order Value (the one I want to display)
01/01/20182015
04/02/20182020
03/03/20182020
05/04/20182020

etc

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

Kind regards,

James

3 Replies
olivierrobin
Valued Contributor II

Re: Subtract value from first occurence of data

hello

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

jamestaylor52
New Contributor II

Re: Subtract value from first occurence of data

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 ) ).

jamestaylor52
New Contributor II

Re: Subtract value from first occurence of data

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