Inter Records Analysis

[#1]

Hello,

I have a table as follow: TicketID, Sales.

The tickets are ordered by ticketID ascending.

I would like to create a chart (straight table) which shows you the ticketid and its sales. However, the condition is: If the next ticket's sales is larger than $100. How can I create this inter-record expression? Will set-analysis help in this case?

Thanks...

[johnw]

I'm not sure I follow.  But maybe something like this?

if(below(sum(Sales))>100,sum(Sales))

[#3]

I thought about below/above. However, this works only if TicketID is the dimension in this chart. The real example is more complex: The table contains another dimension: Store. For each store, there's a bunch of tickets and sales. Now, I would like to display how many tickets each store had which answer the condition I described above.

How can that be achieved?

[johnw]

So, Store is your dimension?  For each store, you want a count of how many tickets that store had with sum of sales > $100?  I think this:

sum(aggr(if(sum(Sales)>100,1),Store,Ticket))

Hmmm, but you said you wanted to see the ticket ID, so I'm probably not understanding it any better this time than before.

[#5]

I'll try to explain by example:

My source table is like this:

Store, ticketid, sales

1, 100001, 50

1, 100002, 120

1, 100003, 70

1, 100004, 80

2, 100030, 10

2, 100032, 130

2, 100033, 140

My chart in QlikView should show the following:

Store, # of tickets

1, 1

2, 2

Where # of tickets is the number of tickets that answer the condition. The condition is that the next ticket in line for that store has sales greater than 100. As you can see in that example, store 1 has 1 ticket that matches this condition (ticketid 100001) and store 2 has 2 tickets that match this condition (100030, 100032)

[johnw]

Ah, then I DID understand correctly the second time.  Try the sum(aggr()) solution above.  It works for me on your sample data set.

[#7]

thanks, but not quit there I'm afraid. In your expression, you didn't really check the condition between one ticket and the next one but rather aggregated all tickets. What if the first ticket has sales over 100? It shouldn't be counted though!

In addition, how cna you take it even further and make the condition even more complex, e.g:

Count all tickets that match the following condition - they have sales less than 50 and the next ticket has sales over 100?

Sorry - I'm not just trying to make it more difficult - I'm just slowly proceeding to the logic we actually required to accomplish

[#8]

I think I found the answer:

=sum(aggr(if(sum(sales)<50 AND Below(sum(sales)) > 100,1),store,ticket#))

what do you think?

[johnw]

Ah, OK. I didn't read carefully enough or I'd have noticed that the IDs you were counting were not the ones I was counting.  With your additional condition, I believe the answer is this:

sum(aggr(if(sum(sales)<50 and below(sum(sales)>100),1),Store,ticketid))

To avoid getting in an endless loop of "but what if", I've attached an example.  The first table shows how I went about solving this problem.  I built a table that had both Store and ticketid in it, because I knew I would need to aggregate by those in the final table.  Then I just slowly built the expression up piece by piece.  The second table is the result of this process.  Building the expression up bit by bit like this lets you see where things are going wrong and correct them rather than poking endlessly at a complicated final expression.  By the time I copy the expression into the real table, it usually requires no further revision to work in context.

One possible issue with below() in this context is that I believe it uses the default sort order.  If you wanted your tickets sorted in some other order before looking at the below ticket, I don't think this approach solves the problem. 

[johnw · Accepted solution]

doron.reichenberg wrote:

I think I found the answer:

=sum(aggr(if(sum(sales)<50 AND Below(sum(sales)) > 100,1),store,ticket#))

what do you think?

Hah.  I guess I posted just a couple minutes too late.  Yes, that's exactly right.  Good job.