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 · Accepted solution]

Okay so you have more than one dimension you are checking this on, right? Try this

Sum(Aggr(If(Sum(Importo) > 15000, Sum(Importo), Dim1, Dim2))

[paolocamerini]

Bingo! Looks a lot better. Now it's time to validate the result! Thank you fellow Qlik genius!

[paolocamerini]

Only disadvantage is that the size of the application has increased a lot. I have a couple of questions:

1. How can this method not create duplicates throughout the records? It looks like the rows (in this case) are repeated 7 times
2. I have no new synthetic keys, how so?

[sunny_talwar]

I think size of the dashboard should be not big of a concern considering this will give you much better performance on the front end.

1) They are repeated on the front end, but the back end data stays intact because the fact table have stayed as is, it is the link table (AsOfTable) which have this multiple logic.... one day (asofdate) is conneted to 7 days.

2) No synthetic keys because you are joining on just Data field.....