10 Replies Latest reply on Mar 9, 2016 8:29 AM by jitan.chainani

Merge 2 case statements into one column

Hi Guys,

Thanks for looking at this.  If you check the source there are couple of multiple  values columns  Multi 1, Multi 2, Multi 3, Multi 4, weekend 1, weekend 2 and weekend 2 multi.

These multiples are used to calculate estimates for week 1 and week 2.

Based on these multiples I calculate the estimates and projections for next 7 days for 2 weeks only.

The request is to bring Estimate Week 1 and Estimate Week 2 in one column just side by side to week 1 and week 2.

So when the user selects the parameter for week 1 or week 2 the numbers update for just week 1 or week 2.

Also, it would be great if i can add another projection  row based on user input by territory. i.e user should be able to enter the multiple value on tableau and the estimates and projections are automatically updated.  This could be a different row.

So we have 2 rows for projections

One calculated by the tableau and parameter selection and the other by user inputs. i think we will also need a new  estimate place if user decides to input their number.

Note - The projections are calculated based of estimates and multiples are used to calculate estimates.

• 1. Re: Merge 2 case statements into one column

Hi Jitan-

Here is the logic for combining those CASE statements for your estimate into one column.

Estimate

IF [Week (Raw Data)] = "Week 1"

THEN [Amount] /

IF [Select Multiple Week 1] = 'Weekend 1' THEN [Weekend 1]

ELSEIF [Select Multiple Week 1] = 'Multi1' THEN [Multi1]

ELSEIF [Select Multiple Week 1] = 'Multi2' THEN [Multi2]

ELSEIF [Select Multiple Week 1] = 'Multi3' THEN [Multi3]

ELSEIF [Select Multiple Week 1] = 'Multi4' THEN [Multi4]

END

ELSEIF [Week (Raw Data)] = "Week 2"

THEN [Amount] /

IF [Select Multiple Week 2] = 'Weekend 2' THEN [Weekend 2]

ELSEIF [Select Multiple Week 2] = 'Weekend 2 Multi' THEN [Weekend 2 Multi]

END

END

I am not sure I am following your next request.  If you want to clarify a bit more or provide a sample output, I'll take a crack at it.

9.0 workbook attached.

2 of 2 people found this helpful
• 2. Re: Merge 2 case statements into one column

Thanks for the solution. Really Appreciate your help.

In the next request, basically users wants to input multiples for week 1 and week 2 to calculate estimates by themselves if they think the multiples are different.

We could add one more row to the above screen shot and  a numeric parameter box on the right  so that the user can change the estimates by adding multiples by territory.

They want to see database driven estimates which is already added and a user input estimates if they think the multiple should be different.

JC

• 3. Re: Merge 2 case statements into one column

Here is what I came up with for that, maybe you can tweak it for your needs.  I added three new parameters.  When the override is on generated, it will calculate as before.  When it is on override, it will use the week1 and week2 override values.

I tweaked the estimate, week1 estimate and week2 estimate formulas in this version.

9.0 version again.

2 of 2 people found this helpful
• 4. Re: Merge 2 case statements into one column

Thanks again for your help. Quick question -

Can we do the override at country level? For e.g. the user wants to change just one country and not others.

Thanks,

JC

• 5. Re: Merge 2 case statements into one column

you could change the override from yes to the county name.  Then something like if county name equal parameter then use override figures.  But they single value parameters.

1 of 1 people found this helpful
• 6. Re: Merge 2 case statements into one column

I am trying to work on the above logic you mentioned at country level. Created a country parameter and updated the Estimate statement.

I am getting it to work but it just updates for one country. and the remaining country estimate changes to Null.

Can you please let me know what's missing?

• 7. Re: Merge 2 case statements into one column

Hi Jitan-

Unfortunately, the parameters do not have the capability for multi-select.  Please vote here: Multi-Values Parameters you will be #817.

I don't know how many countries are in your real data, but if it is only a handful, you could create multiple parameters and work the logic into a calculated field.  If it is many, maybe it is possible to get an override indicator in your data source?

2 of 2 people found this helpful
• 8. Re: Merge 2 case statements into one column

Thanks for your response. I will go ahead and vote for the multi-parameter.

One more quick question - If you look at the screen shot below I am calculating projected amount 1 based of avg. projections for new releases.

The same calculation I do in excel and is different value.

so for day 1 the projected amount in excel is 45,627 and tableau is calculating 443,012. (5% of 3050847(

Would you know why different answers? Although the numbers are close.

• 9. Re: Merge 2 case statements into one column

Hard to say without looking at the worksheet.  Looks like this version has some data blending going on where the one I just opened is joined.  It could be due the relationship - I can't tell if there are filters too.

It could also have something to do with the # of records and the average calc, but I can't say for sure.