Calculated field with conditional

[Anonymous]

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

[sunny_talwar]

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

[vinieme12 · Accepted solution]

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 .

[Anonymous]

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  

[Anonymous]

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.

[sunny_talwar]

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]

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.

[Anonymous]

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

[Anonymous]

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.

[sunny_talwar]

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

Best,

Sunny