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!

[YoussefBelloum]

Hi,

everything is clear except this: in the very next 7 days ??

[paolocamerini]

That is to say: when Data is 01/09/2017 I want it to count the TicketID from date 01/09/2017 to date 07/09/2017 (which are 6) and so on.

[sunny_talwar]

I suggest using as of table here

Table:

LOAD * INLINE [

    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

];

AsOfTable:

LOAD Data as AsOfData,

Date(Data + IterNo() - 1) as Data

Resident Table

While IterNo() <= 7;

[YoussefBelloum]

Ok and the first date is date you select on the filter data or it is the min date of your table ?

[paolocamerini]

Attachment seems related to another discussion!

[stabben23]

Here is another example used by dummydata, use preceding load twice.

LET vEndDate = date(addmonths(today(),0)-7); //Variable to hold 7 days back= >2018-02-28

Load *
where SumAmount >9;
LOAD Data, PersonID, count(TicketID) as ID, Sum(Amount) as SumAmount
where Data >'$(vEndDate)'
group by Data, PersonID
;
load * Inline [
Data, TicketID, PersonID, Amount
2018-03-07, 1, BGXXXXX1A, 4
2018-03-06, 2, BGXXXXX1A, 7
2018-03-06, 3, BGXXXXX1A, 2
2018-03-05, 4, BGXXXXX1A, 1
2018-03-05, 5, BGXXXXX1A, 10
2018-03-03, 6, BGXXXXX1A, 11
2018-03-02, 7, BGXXXXX1A, 12
2018-03-01, 8, BGXXXXX1A, 1
2018-02-28, 9, BGXXXXX1A, 1
];

[paolocamerini]

It's the date related to each and every TicketID and yes, it can be selected in the filter data

[sunny_talwar]

Fixed it....

[paolocamerini]

I can't make my dataset any more complex than it actually is. If there's no solution with the dataset provided I'll give up