topic Pivot - Expression from properties to syntax in QlikView

Pivot - Expression from properties to syntax

Anonymous — Thu, 07 May 2015 15:28:56 GMT

Hello, we have a review process to publish our apps on the server and I have received a mark for having my Count(disitnct(ID)) in an expression and not in the syntax, so I created Count(disitnct(ID) as CountID in my script but when I add this into my Pivot chart, all I get is 1's for the sum. Any thoughts to why and suggestions on how to fix?

Re: Pivot - Expression from properties to syntax

Thu, 07 May 2015 15:31:37 GMT

Can you post an example qvw?

Thanks

Re: Pivot - Expression from properties to syntax

Anonymous — Thu, 07 May 2015 15:46:26 GMT

I would be happy to but never attached an app on here before. Could you provide steps on how to do so? Sorry to be so needy!

Re: Pivot - Expression from properties to syntax

buzzy996 — Thu, 07 May 2015 15:56:30 GMT

1. click Reply

2. on tor right corner there is option "Use Advanced editor"--click on tht

3. from bottom whr ur trying to write msg --thr is option Attach--clik on that

4. select ur file---thr is a buton in bottom --clik that button.

Re: Pivot - Expression from properties to syntax

Anonymous — Thu, 07 May 2015 16:18:31 GMT

Okay, see attached but of course I cannot get the expression to work in the script.

Again, I just trying to move the expression from the chart properties Pivot into the script.

Re: Pivot - Expression from properties to syntax

tcullinane — Thu, 07 May 2015 16:26:55 GMT

check out Henric Cronstroms 'A Myth About Count(distinct …)' post and tell them to give you your marks back

Re: Pivot - Expression from properties to syntax

Thu, 07 May 2015 16:41:58 GMT

I think you have to group the expression by dimension, try this:

test:

LOAD Idtest,

//Count(DISTINCT Idtest)as countIdtest,

[Location ID]

FROM

qlikviewtest.xlsx

(ooxml, embedded labels, table is Listing);

Test2:

load [Location ID]

,Count(DISTINCT Idtest)as countIdtest

Resident test

Group By [Location ID];

DROP Table test;

Re: Pivot - Expression from properties to syntax

Anonymous — Thu, 07 May 2015 18:01:37 GMT

Right, I was wondering why the expression wan't working. Anyway, so I have attached. See Expr Chart columns, why is count(distinct(idtest)) and countidtest sum differnt in the chart? In my mind they should read the same.

Bascially, I need to remove the expression from the chart and add the expression "in the back end" according to IT

Re: Pivot - Expression from properties to syntax

Anonymous — Thu, 07 May 2015 18:31:26 GMT

I'll get clarification from IT but in the mean time, I'll assume a SUM aggr will do the trick on the that I createted on the syntax.

(DISTINCT Idtest)AS countIdtest

Re: Pivot - Expression from properties to syntax

Fri, 08 May 2015 07:07:42 GMT

Yess, if you need to add "dimensionality" in your expression you should use Aggr function.

But Aggr function only perform a virtual aggregation, without any calculation. If you want to compute, for example, the "sum of the Location ID totals", you must Sum the aggregations.

Example: Sum(Aggr(Count(DISTINCT Idtest),[Location ID]))