topic Re: Counting issue in QlikView

Counting issue

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

Dear Sir/Madam

Can someone advise the following ASAP :

1. Why there is a 1 under Strongly Disagree column (2nd last row) ?

2. How to delete the 2nd last row and KEEP the Strongly Disagree column without data ?.

Thank you

Tracy

Re: Counting issue

qlikviewwizard — Thu, 26 Jul 2018 02:40:01 GMT

Hi,

Please check this.

//Calendar

Let vLYear=(year(date(today(0)))-1);

LOAD * INLINE [

Month, Monthname, Qno

1, Jan, Q1

2, Feb, Q1

3, Mar, Q1

4, Apr, Q2

5, May, Q2

6, Jun, Q2

7, Jul, Q3

8, Aug, Q3

9, Sep, Q3

10, Oct, Q4

11, Nov, Q4

12, Dec, Q4

];

Response:

CrossTable(Qno, Data)

LOAD *

FROM

[Test-1.xls]

(biff, embedded labels, table is Response$);

Answer:

LOAD Code as Badge,

Answer

FROM

[Test-1.xls]

(biff, embedded labels, table is Answer$);

LOAD Dimension,

Qno

FROM

[Test-1.xls]

(biff, embedded labels, table is Questionnaire$);

Re: Counting issue

tracycrown — Thu, 26 Jul 2018 15:09:09 GMT

Dear Mr Arjun Rao

Thank you for your quick response.

Sorry to say that the answer is not correct. I am trying to count number of people who voted Strongly Agree, Agree ....

so sum(Data) is not correct, please advise

Correct Answer for Q1 should be Neutral (6), Agree (6)

Thank you

Tracy

Re: Counting issue

sunny_talwar — Thu, 26 Jul 2018 15:20:34 GMT

How about creating 5 expression after removing Answer as one of the dimension. See the bottom chart

Re: Counting issue

tracycrown — Thu, 26 Jul 2018 15:34:56 GMT

Dear Sunny Talwar

Yes, your answer is correct but is there a better way as too many coding in the expression ?.

Thank you

Tracy

Re: Counting issue

tracycrown — Thu, 26 Jul 2018 15:38:25 GMT

Dear Sunny Talwar

Can you advise why my method created a 1 in Strongly Disagree column ?

Thank You

Tracy

Re: Counting issue

sunny_talwar — Thu, 26 Jul 2018 15:39:43 GMT

There might be... but it won't be clean and will need a lot of trial and error.... I have always had problems with this kind of thing in pivot table... why do you not like this again? a lot of coding? just added set analysis.... that should not be a lot of coding or are you saying your expressions are much more complicated then this?

Re: Counting issue

sunny_talwar — Thu, 26 Jul 2018 15:40:48 GMT

Picking it from this Answer tab of the Excel sheet

Re: Counting issue

tracycrown — Thu, 26 Jul 2018 15:49:30 GMT

Dear Sunny Talwar

But nobody voted Strongly Disagree, so the count(Code) should be ZERO instead of 1, isn't it ?

Thank you

Tracy

Re: Counting issue

sunny_talwar — Thu, 26 Jul 2018 15:52:01 GMT

Right, but you are counting Code... which has a value for Strongly Disagree... but this fall under null dimension (at the bottom)

Re: Counting issue

tracycrown — Thu, 26 Jul 2018 16:02:17 GMT

Dear Sunny Talwar

Since there is nothing on Strongly Disagree (ie. for Code = 1) , it should be counted as 0 instead of 1.

May be there is a bug in Qlikview ???

Best Rdgs

Tracy

Re: Counting issue

sunny_talwar — Thu, 26 Jul 2018 16:03:31 GMT

From what I see, I don't think it is a bug...

Re: Counting issue

tracycrown — Thu, 26 Jul 2018 16:09:32 GMT

Dear Sunny Talwar

I have purposely changed one response as Strongly Disagree, my method is perfectly ok now. I really cannot explain why ?.

Please see attached.

Best Rdgs

Tracy

Re: Counting issue

sunny_talwar — Thu, 26 Jul 2018 16:12:13 GMT

I agree with you, but the result you saw previously isn't wrong also... that is just the way QlikView is...and that is why the alternative