Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 .
Can you show how does your raw data looks like and what you are getting?
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 .
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
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.
May be like this:
LOAD Department,
YYYYMM,
Sum(If(Category='Sales', Amount) - Sum(If(Category='Costs', Amount) as Profit
FROM....
Group By Department, YYYYMM;
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.
Ok, I understand, I didn't know how does it work exactly. Thank you for your hints Vineeth.
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.
Then I would suggest marking his response as correct instead of mine
Best,
Sunny