April 28, 2016 at 10:16 am
I will first of all draw out my issue:
EmpID Date FieldName FieldValue Total
1234 1/1/16 approver John 300
1234 2/1/16 approver tom 400
1234 1/1/16 cost center ab 300
1234 2/1/16 cost center ab 400
This is what I want to get:
2016 Total: 700
April 28, 2016 at 10:36 am
mbraman (4/28/2016)
I will first of all draw out my issue:EmpID Date FieldName FieldValue Total
1234 1/1/16 approver John 300
1234 2/1/16 approver tom 400
1234 1/1/16 cost center ab 300
1234 2/1/16 cost center ab 400
This is what I want to get:
2016 Total: 700
1/1/6 Total: 300
The values are shown across property when one field value is selected. however, we don't want to sum up the total for the whole year to equal 1400. The actual total for the year is 700. The "Field..." are attributes of the date/person.
The row comes in like this
1234 1/1/16 john ab 300
1234 2/1/16 tom ab 400
So the total is 700 not 1400.
The data flow is - data in sql server goes to tabular model to tableau.
The "Field..." are filters in tableau.
So what is happening when you select 1 value and 1 name - its correct.
It's when you select all names and all values that it starts summing.
It's almost like a hierarchy. However I cannot get this to work.
Any suggestions? Does this explain it or do I need to add more detail?
is there something in sql server that can be done to accomplish this so it just goes straight into the tabular model and tableau?
It's not totally clear what you are asking for.
If you give us a data set and ask how to query it to get certain results, we can help.
If you are asking for help with configuring Tableau, you should probably ask in a Tableau forum.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 28, 2016 at 10:42 am
The data I presented is pretty much it.
I want to be able to query for a total for the year 2016 without it adding up all 4 rows. The total for the year is actually 700 and not 1400.
The data comes in as one row but i have pivoted it be in one column for name and one column for value resulting in the over summing.
this could possibly something to post in a tabular model, however, I'm wondering if there are any sql tricks that will not sum up all of those rows when I'm asking just for the total.
April 28, 2016 at 10:59 am
mbraman (4/28/2016)
The data I presented is pretty much it.I want to be able to query for a total for the year 2016 without it adding up all 4 rows. The total for the year is actually 700 and not 1400.
The data comes in as one row but i have pivoted it be in one column for name and one column for value resulting in the over summing.
this could possibly something to post in a tabular model, however, I'm wondering if there are any sql tricks that will not sum up all of those rows when I'm asking just for the total.
select ...
where FieldName = 'Approver'
would do that. There are no SQL tricks which will ignore certain values under unspecified conditions, presumably using advanced AI. You really need to be more specific.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 28, 2016 at 11:01 am
A couple things. First, use the code=plain code on the side to format your tables and use spacing so we can read things. Like this:
EmpID Date FieldName FieldValue Total
1234 1/1/16 approver John 300
1234 2/1/16 approver tom 400
1234 1/1/16 cost center ab 300
1234 2/1/16 cost center ab 400
Next, you can limit the rows, but choosing a value from the FieldName to limit returns, but you'd have to do that in code somewhere.
select sum(total)
from table
where empid = 1234
and FieldName = 'approver'
Or some variation. If you've pivoted data, then you've duplicated it and doubled values. If you want to prevent that, you need some limiter (WHERE clause)
April 28, 2016 at 11:54 am
mbraman (4/28/2016)
So the total is 700 not 1400.The data flow is - data in sql server goes to tabular model to tableau.
The "Field..." are filters in table.
So what is happening when you select 1 value and 1 name - its correct.
It's when you select all names and all values that it starts summing.
It's almost like a hierarchy. However I cannot get this to work.
I think the major problem here is that you don't understand your data well enough to understand what you should and should not be pulling. As others have mentioned, your query rests entirely on the WHERE clause. But you can't filter appropriately if you don't understand what the difference is between the "good" data that needs to be in your result set and the "bad" data that needs to be excluded.
I'm assuming you scrubbed the data you posted in this thread. Which means us telling you to search on "Approver" isn't going to help you in the office if that's not actually the difference between the good stuff and the bad stuff.
Your best bet is to go to your boss or end users (whomever assigned you this report) and ask them to identify the difference between the good and bad data. If they cannot identify it and only know they want a number that is different from the number in the table, then you can't give them what they want no matter how hard you work at this.
If they can identify it, however, talk with them about the data until you understand what it is they are looking for. Then come back to your desk and use the WHERE clause to filter appropriately.
April 28, 2016 at 11:58 am
I apologize - it's been very difficult to explain. No need to reply anymore.
Thank you.
April 28, 2016 at 2:08 pm
It appears your table has submitted expenses and approved expenses.
So in the end, you have 700 submitted and 700 approved.
If that is the case, you may want to think about how that impacts the overall solution.
They will not always be the same - you could have open amounts not approved yet.
Or situations where an amount is submitted, but not all is approved.
I'd look closer into data.
And what the end users expect to see and how they use it.
When I have seen this type of data, it was usually modeled in with Order Number, Line Number, Amount, and a status (open or paid) at a minimum, in 1 line.
April 29, 2016 at 4:17 am
mbraman (4/28/2016)
I apologize - it's been very difficult to explain. No need to reply anymore.
I hope you're not giving up because of what I said. The intent was not to refuse to answer the question, but to point out something that needed to be considered.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply