3 Replies Latest reply on Aug 31, 2018 6:07 AM by albert wong

# How can I combine multiple fields without concatenating labels assigned in calc if more than (1) field meets the criteria?

Hello Jedis,

I have (6) Labels i need to capture totals for that have a completed date of prior business day into one sheet on a bar graph.

The fields are: A, C, F, L, S, U

The only thing that got us close to what we wanted was to use a calc to group true items below:

if [Starts Pior Day] = '1' then 'S' ELSE '' END + if [UW Submitted Prior Day] = '1' then 'U' ELSE '' END + if [Locks Prior Day] = '1' then 'L' ELSE '' END + if [Cond. Approval Prior Day] = '1' then 'C' ELSE '' END + if [Approval Prior Day] = '1' then 'A' ELSE '' END + if [Funded Prior Day] = '1' then 'F' ELSE '' END

The tricky part is, when more than one label meets the criteria, it concatenates those values instead of calculating them into the "Fields" described.

For example, below we have each label with correct totals EXCEPT the one which had (2) true values which automatically labeled it "UL" when it should have counted as 1 for "U" and 1 for "L".

Any suggestions?

Thanks ahead of time!!

@

• ###### 1. Re: How can I combine multiple fields without concatenating labels assigned in calc if more than (1) field meets the criteria?

Any chance of sharing a packaged workbook?

1 of 1 people found this helpful
• ###### 2. Re: How can I combine multiple fields without concatenating labels assigned in calc if more than (1) field meets the criteria?

I had to bring 'Measure Names' to the MARKS card.

1 of 1 people found this helpful
• ###### 3. Re: How can I combine multiple fields without concatenating labels assigned in calc if more than (1) field meets the criteria?

Hi Ken,

Unfortunately i can't upload the workbook but can show a screenshot of what we did.

We first created (6) separate measures for each column with a calculation like so:

Then we brought the Measure Names to Columns and Measure Values to Rows.

And SUM each column in the measure values.

The Measure Names went into the FILTERS card and we selected the ones where we labeled them 'prior day' hence giving us the final result we wanted which looks like this.

2 of 2 people found this helpful