Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Tips to do a cross dimension calculation?

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

1 Solution

Accepted Solutions
chriscammers
Partner - Specialist
Partner - Specialist

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.        

View solution in original post

6 Replies
chriscammers
Partner - Specialist
Partner - Specialist

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.        

erichshiino
Partner - Master
Partner - Master

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

Not applicable
Author

Hi,

Have look at the attached application.

Hope this may help you.

- Sridhar

chriscammers
Partner - Specialist
Partner - Specialist

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.

Not applicable
Author

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;

Not applicable
Author

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