topic Re: Expression as Dimension in QlikView

Expression as Dimension

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

Hello,

I have this expression: count(distinct x)/Count(distinct TOTAL(x)).

And i would like to see have the results on dimension, however when i put this expression on dimension it gives me an error.

How can it be done?

Can you give me some pointers?

thank you

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 18:07:44 GMT

aggr(count(distinct x)/Count(distinct TOTAL(x)),yourdimension1[, ...])

It ist not possible to use aggregation functions in dynamic dimensions without aggr(), because "the aggregation area" ist not clearly defined else

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 18:13:26 GMT

Hello,

Thank you for your answer but i think that i have not explained well myself.

this expression count(distinct x)/Count(distinct TOTAL(x)). gives me the percentage of clients.

and i want to have that on dimension.

With your formula it doesn´t give me the data i need.

Thank you.

Re: Expression as Dimension

Anil_Babu_Samineni — Thu, 24 Nov 2016 18:16:36 GMT

Can yiu share the application which denomstrates the issue and share expected output please

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 18:19:48 GMT

aggr(nodistinct count(distinct x)/Count(distinct TOTAL(x)),x)

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 18:28:04 GMT

Hello,

with your formula it gives me this graph

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 18:33:16 GMT

could you post some data in excel format please?

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 18:41:30 GMT

Sure,

here you have

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 19:26:36 GMT

please check this

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 19:41:44 GMT

Hello,

Thank you for your help,

on expression i want the sum of sales accumulated. with that there is no problem.

on dimensions i would like the percentage of clients. so it goes from 0 to 100.

i do not know if it can be done.

thank you in advance

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 19:54:02 GMT

???

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 19:58:26 GMT

last idea:

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 20:04:16 GMT

i do not understand the first files dimension data.

i think that the second file is the original one. i do not see any differences.

thank you

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 20:07:21 GMT

the last I uploaded has a scale from 0% to 100% instead of 0 to 1.

This looks now like your Excel file:

Re: Expression as Dimension

Anonymous — Thu, 24 Nov 2016 20:17:02 GMT

You are right, but on the graph they are on the dimension that is why the curve of the graph it is like that, and on your file the curve is not the same.

i would like for example 10% of the clients make 30% of the sales and so on.

Something like that

Re: Expression as Dimension

Anonymous — Sat, 26 Nov 2016 18:07:10 GMT

ok, here's another approach, script based:

UNQUALIFY *;

TEMP:
LOAD * INLINE [
client, sales
1, 5
2, 9
3, 60
4, 50
5, 10
6, 40
6, 40
7, 3
8, 120
9, 1
9, 3
];

NoConcatenate

TEMP2:
LOAD
client,
sum(sales) as sales
Resident TEMP
Group by client;

drop Table TEMP;

NoConcatenate

TEMP:
LOAD
*
Resident TEMP2
Order by sales desc;

drop Table TEMP2;

TEMP2:
LOAD sum(sales) as sum_sales
Resident TEMP;
LET sum_sales= Peek('sum_sales');
drop Table TEMP2;

SET buffer = 0;
SET buffer2 = 0;

FOR i=0 to NoOfRows('TEMP')-1
FINAL:
LOAD
Peek('sales',$(i),'TEMP') as "sales",
RangeSum(Peek('sales',$(i),'TEMP'),$(buffer)) as "sales_cumulated",
num(RangeSum(Peek('sales',$(i),'TEMP'),$(buffer))/ $(sum_sales),'#.##0,00%') as "sales_percent_cumulated",
Peek('client',$(i),'TEMP') as "client",
num(($(i) +1) / NoOfRows('TEMP'),'#.##0,00%') as "client_percent_cumulated"
autogenerate(1);
LET buffer = RangeSum($(buffer),Peek('sales',$(i),'TEMP'));
NEXT

DROP Table TEMP;

Re: Expression as Dimension

Anonymous — Sun, 27 Nov 2016 12:20:50 GMT

with the help of Frank Hartmann, I finally got it also working without scripting:

(Possible getting "RowNo()" of a dimension?!?)