- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate difference between feilds of a column
I am loading multiple fields, YEAR being one of them. YEAR contains 2 values, 2018 and 2019. I am showing sum(sales) as an expression in straight table. Now I want to show the difference of sales in 2 years. How do I do that? I can not use set expression as these years are dynamic. Somehow the data needs to look like -
Other dimensions | YEAR | 2018 | 2019 | difference |
200 | 100 | 100 | ||
300 | 250 | 50 |
How shall I achieve this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try to use something like this:
Normal table (bottom - top)
sum(sales) - below(sum(sales))
sum(sales) - above(sum(sales))
Pivot: (left - right)
sum(sales) - before(sum(sales))
sum(sales) - after(sum(sales))
regards
tim
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am unable to do that because I have only one expression, and multiple dimensions. The column YEAR is a dimension. Where do I try the options you mentioned?
The data needed is as:
2018 | 2019 | diff (This is calculated one) |
100 | 50 | 50 |
200 | 100 | 100 |
2018 and 2019 are values for year.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you need to create another expression like "absolute difference".
there you try the things i mentioned.
regards
tim
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Basically in the year feild, I want one more value as 2018-2019. Tried with creating a different expression, it shows 2 columns with difference. One for 2018 and anathor for 2019
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you simply need the difference between two columns, you can:
- edit chart and add a new expression
- open the expression editor
- insert your expression: =[Total Sales]-[Gross Profit]
- name the column: ie: Net Sales
- apply and save the chart
Not sure if this is what you are after or not but hope it guides you in the right direction.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Frank, Thanks for the solution, but the issue is that I have only one expression. The values I see on chart are under a dimension containing 2 values.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Frank, This is how my data looks like (dummy data due to restrictions)
The only expression is sum(sales) and I want to add a difference column beside Q1 and Q2, that will tell the difference in sales made in both quarters
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
check this thread
for a workaround where you use the total row to show the difference.
if(Seconddimensionality()=1, Expression for Total, Normal Expression)
You can create a new expression with Seconddimensionality() to see which number you need to use for the if condition:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You mentioned a straight table early on, but this appears to be a pivot table, but I am going to toss out the following to have a look just in case it might work for you:
Regards,
Brett
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.