topic Re: Create total without dimension in QlikView

Create total without dimension

Wed, 07 May 2014 15:59:06 GMT

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.

Re: Create total without dimension

Jason_Michaelides — Wed, 07 May 2014 16:15:41 GMT

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

Re: Create total without dimension

Thu, 08 May 2014 10:55:42 GMT

Jason Michaelides that is great - thank you

Re: Create total without dimension

Thu, 08 May 2014 11:02:55 GMT

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