Excel Indirect

[#1]

Hi,

I have a table (lets call it [Sales] where the column headers are named by years as follows: .[2015]; .[2016]; .[2017] etc

(thus [sales.2015]; [sales.2016] etc)

I have another table (Parent) which has year going downwards i.e. column name = [Year], fields = 2015,2016,2017 etc

(thus [Parent.Year])

I want to be able to put the Parent.Year field as headers on a pivot and sum from Sales table column which equals to parent.year field

sort of like this Sum( $(='Sales.' & Parent.Year & ']' )

except this formula doesnt seem to be working....

please help!

[kkkumar82]

Hi,

Can you post some sample excel file .

Thanks

Kiran

[#3]

Kiran,

Many thanks for reply - unfortunately, unable to post table - the excel reference is simply how i would've tackled this in excel using the indirect function - looking for the qlikview equivalent

[Anonymous]

I think first go for CrossTable Load, see this?

The Crosstable Load

Loading Cross Tables

which will make it simpler!!

Or Else share some sample data?

[#5]

i'd prefer not to go down the cross table load route...

[PradeepReddy]

use cross table option as shown bellow..

Sales:

CrossTable(Year,Sales)

Load * inline

[

Country,2012,2013,2014,2015

India,200,300,400,500

USA,300,400,500,600

Canada,400,500,600,700

];

[jonathandienst]

Your data model is not clear from your post. Any suggestions made are pure guesses. I suggest that you share your qvw file or a representative sample and more information about what you are trying to achieve.

[#8]

Period Table:

Year

2015

2016

2017

Sales Table:
Cost Centre	Sales_2015	Sales_2016	Sales_2017
1	8	7	1
2	6	1	4
3	7	4	2

Desired Outcome: Pivot Table in QV:
2015	2016	2017
=sum(["Sales_" & Year&"])