Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paolocamerini
Partner - Contributor III
Partner - Contributor III

Help with count (with conditions)

Hello everyone,

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

     

DataTicketIDPersonIDAmount
01/09/20171BGXXXXX1A4
05/09/20172BGXXXXX1A7
05/09/20173BGXXXXX1A2
06/09/20174BGXXXXX1A1
06/09/20175BGXXXXX1A10
07/09/20176BGXXXXX1A11
08/09/20177BGXXXXX1A12
11/09/20178BGXXXXX1A1
15/09/20179BGXXXXX1A1

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:

     

DataIDCountSumAmount
01/09/2017BGXXXXX1A635
05/09/2017BGXXXXX1A1144
06/09/2017BGXXXXX1A535
07/09/2017BGXXXXX1A324
08/09/2017BGXXXXX1A314

[...]

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!

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

23 Replies
YoussefBelloum
Champion
Champion

Hi,

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

paolocamerini
Partner - Contributor III
Partner - Contributor III
Author

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;

Capture.PNG

YoussefBelloum
Champion
Champion

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

paolocamerini
Partner - Contributor III
Partner - Contributor III
Author

Attachment seems related to another discussion!

stabben23
Partner - Master
Partner - Master

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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