topic Re: Tips to do a cross dimension calculation? in QlikView

Tips to do a cross dimension calculation?

Sat, 14 May 2011 11:25:57 GMT

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

Re: Tips to do a cross dimension calculation?

chriscammers — Sat, 14 May 2011 13:32:03 GMT

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.

Re: Tips to do a cross dimension calculation?

erichshiino — Sat, 14 May 2011 14:28:25 GMT

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

Re: Tips to do a cross dimension calculation?

Sat, 14 May 2011 14:34:13 GMT

Hi,

Have look at the attached application.

Hope this may help you.

- Sridhar

Re: Tips to do a cross dimension calculation?

chriscammers — Sat, 14 May 2011 16:14:58 GMT

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.

Re: Tips to do a cross dimension calculation?

Mon, 16 May 2011 10:05:18 GMT

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;

Tips to do a cross dimension calculation?

Tue, 17 May 2011 21:27:48 GMT

Thank you all very much for your useful suggestions. I like having the calculation at the load time, avoiding calculations in the report itself.