Help with count (with conditions)

[paolocamerini]

Hello everyone,

I have a dataset (an inch more complex than this but hopefully this will serve) like this:

Data	TicketID	PersonID	Amount
01/09/2017	1	BGXXXXX1A	4
05/09/2017	2	BGXXXXX1A	7
05/09/2017	3	BGXXXXX1A	2
06/09/2017	4	BGXXXXX1A	1
06/09/2017	5	BGXXXXX1A	10
07/09/2017	6	BGXXXXX1A	11
08/09/2017	7	BGXXXXX1A	12
11/09/2017	8	BGXXXXX1A	1
15/09/2017	9	BGXXXXX1A	1

I need to count for each Data the number of the distinct TicketID that I can find in the very next 7 days but only if the sum of the amount exceeds 25.

I'm expecting this output:

Data	ID	Count	SumAmount
01/09/2017	BGXXXXX1A	6	35
05/09/2017	BGXXXXX1A	11	44
06/09/2017	BGXXXXX1A	5	35
07/09/2017	BGXXXXX1A	3	24
08/09/2017	BGXXXXX1A	3	14

[...]

Last 2 rows should not appear for their amount is less than 25.

Can't really find a way to achieve my result.

Could anyone of you please help me out?

Thank you!

[sunny_talwar]

There is, but this is probably the best way to handle your situation... others are going to be very slow on the front end... if you would like... I can guide you there as well... but look here before you think of going there

The As-Of Table

[paolocamerini]

I tried to create the AsOfTable in my datamodel, however I'm getting the sum of the amounts for every record and not only those whose sum is larger than "x".

I'm in a daze

[sunny_talwar]

Did you look at the expression that I have used?

[paolocamerini]

Sure. I noticed that in your example, AsOfData takes the place of Data. In my application this doesn't happen.

[sunny_talwar]

But did you not create a AsOfDate in your AsOfTable?

[paolocamerini]

This is what I get after creating the AsOfTable

Data	AsOfData
01/09/2017	26/08/2017
01/09/2017	27/08/2017
01/09/2017	28/08/2017
01/09/2017	29/08/2017
01/09/2017	30/08/2017
01/09/2017	31/08/2017
01/09/2017	01/09/2017
02/09/2017	27/08/2017
02/09/2017	28/08/2017
02/09/2017	29/08/2017
02/09/2017	30/08/2017
02/09/2017	31/08/2017
02/09/2017	01/09/2017
02/09/2017	02/09/2017
03/09/2017	28/08/2017
03/09/2017	29/08/2017

[...]

[sunny_talwar]

Right, so why can't you use this as your expression?

=Sum({<AsOfData = {"=Sum(Amount) > 25"}>}Amount)

[paolocamerini]

In my application, using that expression, results in showing every single record (x7 times)

[sunny_talwar]

What happens if you don't have AsOfData as your dimension?

[paolocamerini]

It sums the amounts correctly but shows values lower than 15000 too