Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
As newbie in Qlikview I am currently trying to found it's possibilities.
I have the following data set:
Measure Year Month Store Value
Sales 2010 Jan 1 100
Profit 2010 Jan 1 10
Sales 2010 Feb 1 200
Profit 2010 Feb 1 25
I want to create pivot table, which next to sales, profit also contains profit % (= profit/sales) for the shown store/month. In other words to do a calculation across the dimension based on the shown selection in other dimensions.
Is this possible in Qlikview?
Really appreciate your help in this!
BR,
Jeroen
If I were you I would not use this data set "as is." There are a couple of fundamental things you should do...
First, I would split the "value" field into two fields based on your record type(sales and profit)
So I would do something like this
Table1:
Load
Year,
Month,
Store,
Value AS Sales
From XYZ
Where Measure = 'Sales'
Then you are going to do a full outer join just in case you have any sores, months or years without sales(unlikely, but no system is perfect)
Join (Table1)
Load
Year,
Month,
Store,
Value AS Profit
From XYZ
Where Measure = 'Profit'
So now you end up with something like this...
Year Month Store Sales Profit
2010 Jan 1 100 10
2010 Feb 1 200 25
You should be able to do most of your calculations in your charts
You could also do this without transforming your data using Set Analysis but I definitely would not reccomend that approach.
If I were you I would not use this data set "as is." There are a couple of fundamental things you should do...
First, I would split the "value" field into two fields based on your record type(sales and profit)
So I would do something like this
Table1:
Load
Year,
Month,
Store,
Value AS Sales
From XYZ
Where Measure = 'Sales'
Then you are going to do a full outer join just in case you have any sores, months or years without sales(unlikely, but no system is perfect)
Join (Table1)
Load
Year,
Month,
Store,
Value AS Profit
From XYZ
Where Measure = 'Profit'
So now you end up with something like this...
Year Month Store Sales Profit
2010 Jan 1 100 10
2010 Feb 1 200 25
You should be able to do most of your calculations in your charts
You could also do this without transforming your data using Set Analysis but I definitely would not reccomend that approach.
Hi, I'd recommend using set analysis.
This syntax basically force a selection on the fields:
For your data, it would be something like this.
sum({<Measure={Profit}>}Value)
sum({<Measure={Sales}>}Value)
sum({<Measure={Profit}>}Value) / sum({<Measure={Sales}>}Value)
You can also check the results on the attachment.
Hope it helps,
Erich
Hi,
Have look at the attached application.
Hope this may help you.
- Sridhar
I'm not saying that the set analysis approach will not work but I think you will be making some serious usability compromises in your data model if you don't transform the data. Any user who you share this document with will have to master Set analysis just to produce a simple sales chart. If you separate sales and profit into separate columns then the user only needs to sum the column they want.
Check out the attachment and you can decide what you want to do, each approach will work.
I like Chris's method and his justification is equally matched. Just one question Chris, why exactly do you on the join statement, list all fields again. Could you not just load the Profit part and join.
Would it not still match everything?
TempTable:
LOAD * INLINE [
Measure, Year, Month, Store, Value
Sales, 2010, Jan, 1, 100
Profit, 2010, Jan, 1, 10
Sales, 2010, Feb, 1, 200
Profit, 2010, Feb, 1, 25
];
FinalTable:
LOAD
Month,
Year,
Store,
Value as Sales
RESIDENT TempTable
WHERE Measure = 'Sales';
JOIN (FinalTable)
LOAD
// Month,
// Year,
// Store,
Value as Profit
RESIDENT TempTable
WHERE Measure = 'Profit';
DROP TABLE TempTable;
Thank you all very much for your useful suggestions. I like having the calculation at the load time, avoiding calculations in the report itself.