Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
tuanmaciek
Contributor

Calculated field with conditional

Hi,

I have two columns in my table - category and value. I would like to add in data load editor calculated fields that derives from the category filed:

If(Category='Sales',Amount,0)-If(Category='Costs',Amount,0) as Profit. It gives me strange data, I don't know what's wrong.

Maciek

1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: Calculated field with conditional

use the below in your chart

sum({<Category={'Sales'}>}Amount) - sum({<Category={'Cost'}>}Amount)

if one data row has only one category how will your if statement evaluate per row? Just think!

It will always be Amount for each row .

10 Replies

Re: Calculated field with conditional

Can you show how does your raw data looks like and what you are getting?

vinieme12
Esteemed Contributor II

Re: Calculated field with conditional

use the below in your chart

sum({<Category={'Sales'}>}Amount) - sum({<Category={'Cost'}>}Amount)

if one data row has only one category how will your if statement evaluate per row? Just think!

It will always be Amount for each row .

tuanmaciek
Contributor

Re: Calculated field with conditional

The data showed oboved is also grouped by date and by departments. It's similar to something like that:

Category    YYYYMM    Value          Department

Sales          201601          4000,00    Dep1

Sales          201602          3000,00    Dep2

Costs          201601          3500,00    Dep1

Sales          201603          1000,00     Dep1

Costs          201602          2700,00     Dep2            

Costs          201601          1000,00     Dep1  

tuanmaciek
Contributor

Re: Calculated field with conditional

Thank you Vineeth. You're right, but I would like to have it calculated in my data load editor. I thought it would be calculated like in i.e. SQL table.

Re: Calculated field with conditional

May be like this:

LOAD Department,

          YYYYMM,

          Sum(If(Category='Sales', Amount) - Sum(If(Category='Costs', Amount) as Profit

FROM....

Group By Department, YYYYMM;

vinieme12
Esteemed Contributor II

Re: Calculated field with conditional

but if you do that in script your profit value will remain static , which means your Profit will always be on a level which you GroupBy and won't dynamically change if you add remove dimensions in the chart.

tuanmaciek
Contributor

Re: Calculated field with conditional

Ok, I understand, I didn't know how does it work exactly. Thank you for your hints Vineeth.

tuanmaciek
Contributor

Re: Calculated field with conditional

Vineeth has shown me that I make logic mistake with those dimensions. I didn't know that I have to use group by when I use sum in data load editor, it is similar like in i.e. sql server queries. Thank you Sunny T, the example you showed me also works fine (I'vr checked it) but I have to agree to constraints with group by which I wasn't aware.

Re: Calculated field with conditional

Then I would suggest marking his response as correct instead of mine

Best,

Sunny