topic Re: Help with count (with conditions) in QlikView

Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 10:23:06 GMT

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!

Re: Help with count (with conditions)

YoussefBelloum — Wed, 07 Mar 2018 10:40:20 GMT

Hi,

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

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 10:43:16 GMT

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.

Re: Help with count (with conditions)

sunny_talwar — Wed, 07 Mar 2018 10:46:02 GMT

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;

Re: Help with count (with conditions)

YoussefBelloum — Wed, 07 Mar 2018 10:47:11 GMT

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

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 10:52:55 GMT

Attachment seems related to another discussion!

Re: Help with count (with conditions)

stabben23 — Wed, 07 Mar 2018 10:53:59 GMT

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
];

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 10:54:02 GMT

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

Re: Help with count (with conditions)

sunny_talwar — Wed, 07 Mar 2018 10:54:23 GMT

Fixed it....

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 11:03:35 GMT

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

Re: Help with count (with conditions)

sunny_talwar — Wed, 07 Mar 2018 11:11:57 GMT

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

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 13:39:26 GMT

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

Re: Help with count (with conditions)

sunny_talwar — Wed, 07 Mar 2018 13:43:34 GMT

Did you look at the expression that I have used?

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 13:52:39 GMT

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

Re: Help with count (with conditions)

sunny_talwar — Wed, 07 Mar 2018 13:54:39 GMT

But did you not create a AsOfDate in your AsOfTable?

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 13:58:24 GMT

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

[...]

Re: Help with count (with conditions)

sunny_talwar — Wed, 07 Mar 2018 14:06:38 GMT

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

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

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 14:11:15 GMT

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

Re: Help with count (with conditions)

sunny_talwar — Wed, 07 Mar 2018 14:14:07 GMT

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

Re: Help with count (with conditions)

paolocamerini — Wed, 07 Mar 2018 14:15:25 GMT

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