topic Re: Count Expression in Chart in QlikView

Count Expression in Chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I need help creating a COUNT expression with the attached data set.

Dimension: StartDate_YYYYMM

Expression:

AssignDate IS NOT NULL

Status = Open OR Status = Closed when Enddate_YYYYMM = StartDate_YYYYMM

201608 = 50,213

201607 = 52,543

201606 = 45,026

Re: Count Expression in Chart

saimahasan — Tue, 09 Aug 2016 13:39:51 GMT

You can use the expression as

=count(if(isnull(AssignDate)=0 and (Status='Open' or Status='Closed') and STARTDATE_YYYYMM=ENDDATE_YYYYMM,ID))

Re: Count Expression in Chart

adamdavi3s — Tue, 09 Aug 2016 13:48:14 GMT

Hi,

Hopefully this gives you the required answer?

Re: Count Expression in Chart

Tue, 09 Aug 2016 13:54:15 GMT

i sugest create a flag on scrip and use on set analisys

flag script:

if(len(trim(AssignDate))>0 and ENDDATE_YYYYMM =STARTDATE_YYYYMM,1) as flag

expression:

=Count({< Status={"Open","Closed"},flag={1}>} STARTDATE_YYYYMM)

Re: Count Expression in Chart

adamdavi3s — Tue, 09 Aug 2016 13:56:54 GMT

Does not properly account for Status = Open OR Status = Closed when Enddate_YYYYMM = StartDate_YYYYMM

Re: Count Expression in Chart

adamdavi3s — Tue, 09 Aug 2016 13:59:20 GMT

I agree that adding a flag is a good idea but your solution does not correctly account for Status = Open OR Status = Closed when Enddate_YYYYMM = StartDate_YYYYMM

Flag should be

if(len(trim(AssignDate))>0 and ((ENDDATE_YYYYMM =STARTDATE_YYYYMM and status='Closed') OR (Status='Open')),1) as flag

Re: Count Expression in Chart

Tue, 09 Aug 2016 14:09:11 GMT

for this the for Status = Open OR Status = Closed set analisys make

i think dependes of volum of data for not onarate the scrip a big if

Re: Count Expression in Chart

Anonymous — Tue, 09 Aug 2016 14:28:15 GMT

Thanks. I also need an accumulation of the numbers. Is that possible?

Re: Count Expression in Chart

Tue, 09 Aug 2016 14:31:04 GMT

ramgesum(above(your_expression),0,rowno())

Re: Count Expression in Chart

sunny_talwar — Tue, 09 Aug 2016 14:37:27 GMT

Getting a slightly higher number, but seems to be close, can you check if there are more conditions or if your Excel count was not right?

Expression:

=RangeSum(Above(Count({<AssignDate={'*'}, Status={'Open'}> + <AssignDate = {'*'}, Status={'Closed'}, ID = {"=ENDDATE_YYYYMM = STARTDATE_YYYYMM"}>} ID), 0, RowNo()))

NOTE: Used the sample prepared by adamdavi3s‌ (Thanks)

Re: Count Expression in Chart

Anonymous — Tue, 09 Aug 2016 15:39:28 GMT

So Enddate_YYYYMM = the dimension StartDate_YYYYMM

201608 = AssignDate Is Not Null. Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201608

201607 = AssignDate Is Not Null. Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201607

201607 = AssignDate Is Not Null. Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201606

201608 = 50,213

201607 = 52,543

201606 = 45,026

This is counting an accumulation of customers that were active at some point during the dimension month.

Re: Count Expression in Chart

sunny_talwar — Tue, 09 Aug 2016 15:53:19 GMT

There was close component also, right? Where does that come into picture?

Re: Count Expression in Chart

Anonymous — Tue, 09 Aug 2016 16:31:40 GMT

Sorry, yes. I have updated my reply

Re: Count Expression in Chart

sunny_talwar — Tue, 09 Aug 2016 18:45:35 GMT

So basically there are two conditions?

1) Count ID where AssignDate is Not Null and Status = OPEN

2) Count ID where AssignDate is Not Null and Status = CLOSED and Enddate_YYYYMM = StartDate_YYYYMM?

Re: Count Expression in Chart

Anonymous — Tue, 09 Aug 2016 18:51:14 GMT

Yes, but when I say StartDate = EndDate. I mean for that month.

So Enddate_YYYYMM = the dimension StartDate_YYYYMM

201608 = AssignDate Is Not Null. Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201608

201607 = AssignDate Is Not Null. Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201607

201607 = AssignDate Is Not Null. Status = OPEN + Status = Closed and ENDDATE_YYYYMM = 201606

Re: Count Expression in Chart

sunny_talwar — Tue, 09 Aug 2016 18:56:29 GMT

And which of the two conditions getting accumulated? Both or one of the two?

Re: Count Expression in Chart

Anonymous — Tue, 09 Aug 2016 19:00:04 GMT

Right. Accumulate Condition one: Count ID where AssignDate is Not Null and Status = OPEN.

Condition two is not accumulated.

Re: Count Expression in Chart

sunny_talwar — Tue, 09 Aug 2016 19:08:29 GMT

I have gotten closer , but still not there. Would you be able to show in Excel how you reached those numbers for 201608?

Re: Count Expression in Chart

sunny_talwar — Tue, 09 Aug 2016 19:11:59 GMT

I am getting 49135 for the cumulative open condition and 23 for the non-cumulative closed condition from the Excel file. Which matches with the output for 201608. How are you getting 50,213?

Re: Count Expression in Chart

Anonymous — Tue, 09 Aug 2016 19:34:06 GMT

Sorry, I'm probably making this more diffcult the need be.

49,135 for condition one is correct.

However, I get an additional 1,078 when I filter on AssignDate is still blank/null. Status = Closed and EndDate = 201608 .

For 201607 I get 52,543

Condition 1: 48,315

Condition 2: 4,227 Enddate = 201607, Status = Closed,AssignDate is not blank/null