topic Re: pivottable expression in QlikView

pivottable expression

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

I have a problem with pivot table expression, my table is something like this:

ItemNumber	101_Q	101_A	102_Q	102_A	Total_Q	Total_A	104_Q	104_A	201_Q	201_A	202_Q	202_A	203_Q	203_A
A100	11	11	12	8	23	19	3	0	3	0	12	0	13	0
A101	13	12	13	13	26	25	11	0	4	0	2	0	11	0
A102	2	3	2	9	4	12	6	0	12	0	6	0	12	0
A103	7	10	9	12	16	22	14	0	8	0	15	0	2	0

Ive used CrossTable I would like to calculate 201_A , 202_A and 203_A based on below formula:

201_A=201_Q * (Total_A / Total_Q) abd so on.

my qv file is attached, how can i do that?

Re: pivottable expression

sunny_talwar — Wed, 09 May 2018 18:49:23 GMT

What would be the calculation for 202_A and 203_A?

Re: pivottable expression

Anonymous — Wed, 09 May 2018 18:51:45 GMT

Hi Sunny Talwar

202_A= 202_Q * (Total_A / Total_Q)

203_A =203_Q * (Total_A / Total_Q)

Re: pivottable expression

sunny_talwar — Wed, 09 May 2018 18:56:07 GMT

Are you open to some script changes? not too complicated... just adding a new field which will store the number before '_' in the itemnumber field

Re: pivottable expression

Anonymous — Wed, 09 May 2018 19:02:12 GMT

sorry can you look at my qv file and share it, thanks.

Re: pivottable expression

sunny_talwar — Wed, 09 May 2018 19:03:50 GMT

I can, all I am saying is that are you okay with making script changes?

Re: pivottable expression

Anonymous — Wed, 09 May 2018 19:07:37 GMT

yes I can

Re: pivottable expression

sunny_talwar — Wed, 09 May 2018 19:14:15 GMT

Check the attached

Re: pivottable expression

Anonymous — Wed, 09 May 2018 19:24:53 GMT

sorry could you send the qv file. thank you so much

Re: pivottable expression

Anonymous — Wed, 09 May 2018 19:29:17 GMT

wow what a wonderful solution. thank you so much

Re: pivottable expression

Anonymous — Thu, 10 May 2018 04:06:52 GMT

Mr Sunny Talwar

One more question! how to use background expression in pivot table, for example, I want to use light gray for 101-Q to 104-A Columns.

Re: pivottable expression

kakani87 — Thu, 10 May 2018 04:36:10 GMT

Something like this?

Backgroung exp as - if(match (Transaction,'101_Q','101_A','102_Q','102_A','Total_Q','Total_A','104_Q','104_A'),RGB(192,192,192))

Re: pivottable expression

Anonymous — Thu, 10 May 2018 04:40:32 GMT

That is right.

Re: pivottable expression

Anonymous — Thu, 10 May 2018 07:19:03 GMT

sorry again

in my pivot table like below:

ItemNumber	Transaction	101_Q	101_A	102_Q	102_A	Total_Q	Total_A	104_Q	104_A	201_Q	201_A
A100		11	11	12	8	23	19	0	0	3	2
A101		13	12	13	13	26	25	0	0	4	4
A102		2	3	2	9	4	12	0	0	12	36
A103		7	10	9	12	16	22	0	0	8	11
جمع		33	36	36	42	69	78	0	0	27	53

as you see the 104_Q and 104-A for all item number is zero, thus it should be removed; how to do that?

Re: pivottable expression

kakani87 — Thu, 10 May 2018 07:31:36 GMT

In presentation tab try

check -> Supress Zero-Value

Re: pivottable expression

Anonymous — Thu, 10 May 2018 07:37:50 GMT

It does not work

Re: pivottable expression

Anonymous — Thu, 10 May 2018 07:42:31 GMT

thanks it works

Re: pivottable expression

Anonymous — Thu, 10 May 2018 07:44:21 GMT

but why my it affected my background color?

Re: pivottable expression

Anonymous — Thu, 10 May 2018 07:48:32 GMT

you know for example in some columns zero value does not get background color.