topic Re: Complex expression in Pivot Table in QlikView

Complex expression in Pivot Table

Fri, 15 Mar 2013 01:33:25 GMT

Hi,

I've data in the form attached(ques) & need output as attached(a pivot table)

The problem is that Year has dynamic values which can increase upto 'n' Columns & More cols are needed to be added to the pivot table which are independent of Year.

I think:

1) I've to use only one expression.

2) I can't use Year Dimension

I concatenated the data from two sources,the Typeid field distinguishes the concatenation which i added in the data to identify the sources

as you can see in the ques. that values for Year are blank for TYPEID=2 but still it has to be shown in the same chart with the Year Values(which suggests that year can't be used as dimension,unless you guys have other ideas).

Note that there is only 1 row for 2014....but it can be more,infact Year can increase....(2015 till....n depending on the data)

Pls help

Regards

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 05:34:37 GMT

HELP ME

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 05:47:16 GMT

please upload your sample application

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 06:09:28 GMT

thanks for the reply.

Due to privacy of data it won't be possible for me to post the entire qvw file.

Could you please suggest if i can help in someother way or if you can look at the sample data & output i've posted

Thanks

Re: Complex expression in Pivot Table

Gysbert_Wassenaar — Fri, 15 Mar 2013 09:59:39 GMT

What you want is simply not possible. If you need that kind of output, create it in Excel.

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 10:14:44 GMT

thanks a lot of ur answer.

I was waiting to hear from you.

Is it also not possible to create a a table like this, without using Year as dimenison

& without using set analysis :sum({<Year={2013}>} Amount) as we don't know how many Years

does the Table have ?

Is there anyway we can count the distinct Years in the table and use some expression which will result in adding the cols 2015, 2016...and so on...dynamically as the Year values increase in the table.

May be Genric loading the table to make it a CrossTable and using.........???

DIM1	DIM2	DIM3	2013	2014
-	P	Other	12067442.68	-
A	P	NP	500653.23	-
A	Q	NP	1087656.09	-
A	Q	Other	1431878.856	-
A	Q	ABO	340594.7713	-
A	R	BN	151396.2464	-
B	S	IN	32361573.96	-
B	T	BN	75963108.96	-
B	T	DE	28301155.79	-
B	T	IN	96211986.26	-
B	T	NEW	42647431.91	-
C	U	Other	17808809.6	-
C	U	ABO	4514178.11	-
C	V	BN	2018751.596	-
C	V	Other	14241727.22	-
C	V	ABO	2375031.623	-
D	P	BN	30124452.35	-
D	P	IN	6338915.4	-
D	P	NEW	1108683.55	-
D	P	Other	1139795879	298363331
D	P	ABO	2176092.9	-
D	W	Other	100525025.6	-
D	W	ABO	21158271.19	-
D	W	GRGN	38646728	-
E	X	EX	50000000	-

Thanks again !!!

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 10:20:57 GMT

Hi Qlikviewed,

Can you how many years data you have and which year data you want to show the output

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 10:21:07 GMT

Hi Qlikviewed,

Can you tell me how many years data you have and which year data you want to show the output

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 10:28:39 GMT

This is the problem i'm facing. I also asked the same question to the user but they say that it is dynamic.

May be if I can convince them that the Data later than 2016 is not relevant to you then I might fix something but this is thing of future.

right now it has to be found out form the Data we have.

Any idea?

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 10:36:04 GMT

ok by default which year data you need to show now itself.

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 11:07:10 GMT

Only the Years 2013 & 2014 have values.So it shud like as I pasted above in reply to Gysbert

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 11:10:33 GMT

try this sum({<Year={2013,2014}>} Amount) what about the future.if you fix these years then in future some years of data will add means what will do

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 11:18:13 GMT

the best solution for the user in this case is to have Year as Dimension and use sum(Amount) so that data for every possible Year Value is visible in future, otherwise using what you have mentioned means somebody has to check the possible values for year everytime the application is refreshed & change the set which is a bad option.

Regards

Re: Complex expression in Pivot Table

Fri, 15 Mar 2013 11:20:31 GMT

ya thats the smart way take year as a dimension in pivot table then drag & drop on top line. Thatsolve.