topic Re: Excel Indirect in QlikView

Excel Indirect

Fri, 29 Jan 2016 11:56:55 GMT

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!

Re: Excel Indirect

kkkumar82 — Fri, 29 Jan 2016 12:35:08 GMT

Hi,

Can you post some sample excel file .

Thanks

Kiran

Re: Excel Indirect

Fri, 29 Jan 2016 12:37:56 GMT

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

Re: Excel Indirect

Anonymous — Fri, 29 Jan 2016 12:52:48 GMT

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?

Re: Excel Indirect

Fri, 29 Jan 2016 13:07:12 GMT

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

Re: Excel Indirect

PradeepReddy — Fri, 29 Jan 2016 13:09:56 GMT

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

];

Re: Excel Indirect

jonathandienst — Fri, 29 Jan 2016 13:18:47 GMT

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.

Re: Excel Indirect

Fri, 29 Jan 2016 13:26:23 GMT

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