Create total without dimension

[#1]

Hi i am trying to do a sum of a field without a dimension

My data looks like this:

TranscactionID	CustomerID	OrderValue
1	1111	100
2	2	50
3	455	1
4	455	150
5	455	56
6	2	2467
7	579	269
8	9	364

I want to get the first ordervalue recorded for each customerid and compare it to the most recent ordervalue for that same customer and see if it is greater, if it is i want to count it.

I can then show "95 orders placed that are greater than first order value" in a text object rather than a chart.

How do i do this? Aggr? FirstSortedValue?

Thank you.

[Jason_Michaelides · Accepted solution]

Hi Liam,

Yes, you could use Aggr() with FirstSortedValue().  See attached QVW that uses the following expression:

=Sum(Aggr(If(FirstSortedValue(OrderValue,-TranscactionID,1) > FirstSortedValue(OrderValue,TranscactionID,1),1,0),CustomerID)) & ' orders placed that are greater than the first order value'

Hope this helps,

Jason

[#3]

Jason Michaelides that is great - thank you

[#4]

yes do what jason told to u it is correct i think