Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi,
Can you post some sample excel file .
Thanks
Kiran
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
I think first go for CrossTable Load, see this?
which will make it simpler!!
Or Else share some sample data?
i'd prefer not to go down the cross table load route...
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
];
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.
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&"]) |