Pivot - Expression from properties to syntax

[Anonymous]

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?

[#2]

Can you post an example qvw?

Thanks

[Anonymous]

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!

[buzzy996]

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.

[Anonymous]

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.

[tcullinane]

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

[#7 · Accepted solution]

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;

[Anonymous]

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

[Anonymous]

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

[#10]

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