topic Re: Same expression but different results in App Development

Same expression but different results

r_wroblewski — Thu, 09 Jul 2015 02:22:41 GMT

Dear Community,

Can someone give me a hint why the same expression give different results when I use it in pivot table & text box?

The result in the pivot table is the correct one but I don't understand why using a text box give me a wrong result.

The data is loaded from QVDs and all expressions show correct results, compared to original QlikView application, except the one expression above. Did someone has a hint or idea what could be the problem ?

Best regards,

Ronny

PS: Unfortunately I can't share the QVF or reproduce the error in a separate file until now.

Re: Same expression but different results

vikasmahajan — Thu, 09 Jul 2015 04:49:43 GMT

will you able to give expressions what you are using for arriving this value ?

expession which you use in text is same like used in pivot ?

Vikas

Re: Same expression but different results

r_wroblewski — Thu, 09 Jul 2015 05:05:01 GMT

Hi Vikas,

Yes, expressions are exactly the same, I also replaced the pivot table expression by copy and paste but results are not changing.

Pivot table = 397701

Text box = 6426

Count({<[FY]={$(=Max([FY]))}>} ItemCode)

Could this be a bug ?

Regards,

Ronny

Re: Same expression but different results

vikasmahajan — Thu, 09 Jul 2015 05:23:16 GMT

Hard to say anything sample cab you post sample qvw

Re: Same expression but different results

Thu, 09 Jul 2015 07:10:29 GMT

Please provide the app, it would be much easier to answer

Re: Same expression but different results

Thu, 09 Jul 2015 20:30:19 GMT

Hi,

I your data model, do you have others fields in the item table ? (i.e. : ItemLabel). If so, what appens if you use count on it ?

Re: Same expression but different results

r_wroblewski — Fri, 10 Jul 2015 01:42:23 GMT

Hi Xavier,

yes, [ItemName] is also available and return the same results like [ItemCode].

Pivot table = 397701

Text box = 6426

Regards,

Ronny

Re: Same expression but different results

r_wroblewski — Fri, 10 Jul 2015 01:46:07 GMT

Hi Alexandra

Unfortunately it's not possible to share the application.

I tried to create a sample file but I couldn't reproduce the problem.

Regards,

Ronny

Re: Same expression but different results

Fri, 10 Jul 2015 11:01:02 GMT

What's the result returned by the KPI object ?

Regards,

Xavier.

Re: Same expression but different results

r_wroblewski — Mon, 13 Jul 2015 01:46:36 GMT

Hi Xavier,

I apologize for my late reply.

KPI object also return same value(6424) like text object.

I have absolute no idea why result of text/kpi object is different then pivot table/table when using same expression.

Regards

Ronny

Re: Same expression but different results

sunny_talwar — Mon, 13 Jul 2015 01:59:20 GMT

Ronny are you sure you don't have a DISTINCT in your KPI expression by chance? It seems that the number you are getting is very close to DISTINCT Count of ItemCode. Not sure what is the exact issue, but just trying to throw some ideas

Best,

Sunny

Re: Same expression but different results

r_wroblewski — Mon, 13 Jul 2015 04:02:41 GMT

Hi Sunny,

yes I'm sure that no distinct expression is used instead of Count. I already checked it and also created kpi / textobjects with different count & count distinct expressions.

e.g. for Count({<[FY]={$(=Max([FY]))}>} ItemCode) / Count(DISTINCT {<[FY]={$(=Max([FY]))}>} ItemCode)

object type Count Count(DISTINCT )

text object 6424 6373

kpi object 6424 6373

simple table 397701 6373

pivot table 397701 6373

The raw data is loaded from qvds which are extracted from an qvw. Except this single value all other values are correct and equal to the qvw file. I also checked the raw data but couldn't find any irregularities.

Regards,

Ronny

Re: Same expression but different results

Anonymous — Tue, 14 Jul 2015 09:21:57 GMT

Hi Ronny,

I guess the issue is related to the granularity of the data.

For example, let's have this table

Product, Client

PR_1, CL_1

PR_2, CL_1

PR_3, CL_2

PR_4, CL_3

Now, if I create a table per Product as dimension, and count(distinct Client) as expression then I will get 1 per each product. The total as a sum of the result column is equal 4. If you perform count(distinct Client) in a global scope then the result is 3.

Just an idea.

Thanks,

Janusz

Re: Same expression but different results

r_wroblewski — Wed, 15 Jul 2015 03:42:16 GMT

Hi Janusz,

thanks for your post and I understand what you mean but

I don't think that this is an issue of granularity.

e.g.

pivot table has as dimension only "Fiscal Year" and two measures (QlikView & Qlik Sense)

a)Count(ItemCode)

b)Count({<[FY]={$(=Max([FY]))}>} ItemCode)

both measures return the same result(397,701) in the pivot table for the latest fiscal year in QV & Sense. (expected)

text object (QV & Sense) & KPI object (only Sense)

using Count({<[FY]={$(=Max([FY]))}>} ItemCode) inside a text object in QV return also the correct result (397,701) but using the same expression in Qlik Sense return a strange result which is close to Count(Distinct ...) but I have no idea why

The data is the same, like used in QV, and no modifications done in the Sense script (load of qvds created from the QV app).

Regards,

Ronny

Re: Same expression but different results

Anonymous — Wed, 15 Jul 2015 08:52:46 GMT

Hi Ronny,

Is there any way to limit and scramble the data then load it into the the Sense app and share it here, please?

Thanks,

Janusz

Re: Same expression but different results

r_wroblewski — Wed, 15 Jul 2015 09:09:14 GMT

Hi Janusz,

I tried to create a sample app but couldn't recreate the problem.

I will try to limit & scramble the data when I have time & attach a sample qvf here as soon as possible.

Regards,

Ronny

Re: Same expression but different results

hic — Wed, 15 Jul 2015 19:48:29 GMT

Ronny

My first thought was that ItemCode is a key between two tables, and then Count( ItemCode ) is not well defined. The reason is that the engine doesn't know in which table to make the calculation. So, you need to use Count( disitnct ItemCode ) or Count( CopyOfItemCode ) instead. But then I saw that you have the same problem with ItemName...

Anyway, the way partial sums are calculated has in fact been changed in the new QIX engine. So any new "unwanted" behaviour is extremely interesting for us at Qlik. After all, it could be a bug. So If you still have this problem, I would be very interested in learning more, or maybe get a copy of the app (or a subset of it) to investigate the problem. Naturally we will treat it confidentially. Mail me directly if you want to.

HIC

Re: Same expression but different results

r_wroblewski — Thu, 16 Jul 2015 01:34:08 GMT

Dear Henric

thanks for your reply.

First I will check what will happen if I use "CopyOfKeyFields".

If possible I will create a sample QVW & QVF from the same source file.

The data & apps are in Japanese so I need a little bit more modifications before I can post a useful sample here.

Regards,

Ronny

Re: Same expression but different results

Greg_Hood — Thu, 16 Jul 2015 06:55:56 GMT

Hi Ronny, we have a support team in Japan so language is not a problem. Please PM me and I will send you contact details.

Re: Same expression but different results

r_wroblewski — Thu, 16 Jul 2015 07:38:13 GMT

Dear Gregory,

thanks for your post.

Qlik Support Japan contact information is available.

Basically, I always try to solve my mistakes with help of the community before I contact the support.

I will keep community & support up to date for this issue.

Best regards,

Ronny