Pivot - Create "Calculated Item" like in MS Excel?

[#1]

Hello Everyone!

I'm new at Qlikview and have a problem with working with my data. The raw data (from a database) exists in a form like this:

Values are only in column "Amount".

Now I created a Pivot-Table in Qlikview. I'm showing Net Sales and Cost of Sales in different month. My problem now is that I need to calculate a field "Gross Profit" (=Net Sales - Cost of Sales). Showing the "Total" is not enough, because I have to create different charts including "Gross Profit" (like comparing Net Sales and Gross Profit).

In MS Excel I'm able to create a "Calculated Item" within a field, like here:

Is there anything similar in Qlikview? I tried a lot of expressions, but the "best" I got was a new column with repeated values of Net Sales and Cost of Sales:

I think what I need is a new line/row within the field "Keyfigure". But I don't know if it's possible as all my values are in the raw data are in one column.

Thank you very, very much for your help!

Sandra

[#2]

I couldn't understand ur problem sandra..

Simple "Sum(Amount)" will give same result? I think you created 2 expression.. single expression enough right?

Karthik

[#3]

Tick on "Show Partial Sum" will help you to make total.. that is available in "Presentation" tab

find attachment..

Is this what you looking?

Karthi

[daveamz · Accepted solution]

Hello Sandra,

Yes, it is possible with set analysis.

Build a pivot table with month as dimension and then for expression use:

=sum({<Keyfigure={"1-Net Sales"}>}Amount) - label it 'Net Sales'

=sum({<Keyfigure={"2-Cost of Sales"}>}Amount) - label it 'Cost of Sales'

= [Net Sales]+[Cost of Sales] for Gross Profit

Best regards,

David

[MayilVahanan]

Hi

Dimension:

MonthYear

Expression:

=sum({<Keyfigure={"1-Net Sales"}>}Amount) - 'Net Sales'

=sum({<Keyfigure={"2-Cost of Sales"}>}Amount) - 'Cost of Sales'

= [Net Sales]+[Cost of Sales] for Gross Profit

And then drag and drop by using pivot table.

[#6]

Thank you very much! That solves my problem.

Now I have to show [Cost of Sales] in the table, but that's okay for the moment. In a graph I'm able hide it.

Best regards,

Sandra

[#7]

For all people with similar problems:

I finally solved the problem that I didn't want to show [Cost of Sales] by creating variables with the expressions David suggested and use these variables for further calculations. Now I don't have to hide anything!

[#8]

Thank you for the above reply.  It helps when i have the numeric data in a single field.

My problem is similar to the one mentioned at the start of this thread, with the exception that i have data in multiple fields.  that means, my each month data is in separate fields named JAN, FEB, MAR, etc.  a sample data is given below.  Can you help me to add Gross Profit and Gross Profit% to this in the pivot table?

Your help in this will be highly appreciated.

Thanks.....Abraham