topic Re: Calculated field with conditional in QlikView

Calculated field with conditional

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated field with conditional

sunny_talwar — Tue, 06 Dec 2016 13:33:30 GMT

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

Re: Calculated field with conditional

vinieme12 — Tue, 06 Dec 2016 13:35:10 GMT

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 .

Re: Calculated field with conditional

Anonymous — Tue, 06 Dec 2016 13:41:34 GMT

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

Re: Calculated field with conditional

Anonymous — Tue, 06 Dec 2016 13:46:56 GMT

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

sunny_talwar — Tue, 06 Dec 2016 13:47:28 GMT

May be like this:

LOAD Department,

YYYYMM,

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

FROM....

Group By Department, YYYYMM;

Re: Calculated field with conditional

vinieme12 — Tue, 06 Dec 2016 13:53:18 GMT

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.

Re: Calculated field with conditional

Anonymous — Tue, 06 Dec 2016 14:10:32 GMT

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

Re: Calculated field with conditional

Anonymous — Tue, 06 Dec 2016 14:43:43 GMT

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

sunny_talwar — Tue, 06 Dec 2016 14:45:50 GMT

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

Best,

Sunny

Re: Calculated field with conditional

Anonymous — Tue, 06 Dec 2016 15:07:01 GMT

🙂