8 Replies Latest reply on Sep 3, 2018 8:15 AM by Simon Runc

# Adding value of category to other category

hi all,

i need help in below please,

i have 3 flavored Ice creams :  Chocolate, Vanilla, other

for example : i have 50 chocolate,  30 Vanilla, and 20 Other

i need to add 60% of "Other" to Chocolate (20 * 60% = 12 is added to 50 chocolate and now the chocolate value would show 50+12 = 62) and 40% of Other to Vanilla (20 * 40% = 8 is added to 30 Vanilla and now the Vanilla value would show 30+8 = 38) and eliminate other from the view

Thanks,

LD

• ###### 1. Re: Adding value of category to other category

So I think the easiest way here is to use FIXED LoDs

First we need to get the "Other" sales into the Vanilla and Chocolate Rows....

[Other Sales - Applied to All Rows]

{SUM(IIF([Type]='Other',[Sales],NULL))}

And once we have this we can adjust the sales

IF [Type]='Chocolate' THEN

[Sales]+([Other Sales - Applied to All Rows]*0.6)

ELSEIF [Type]='Vanilla' THEN

[Sales]+([Other Sales - Applied to All Rows]*0.4)

END

I've left "Other" visible so you can see how it all works, but you can just filter it out (as a FIXED LoD the LoD calculation is performed before any filters are applied)

Hope that helps and makes sense.

• ###### 2. Re: Adding value of category to other category

Hi Simon,

Thanks a lot.  infact, i need to count the icecreams and not the sum of sale.

when i replace {SUM(IIF([Type]='Other',[Sales] Count(number),NULL))}

it gives an error "cannot mix aggregate and non aggregate"

• ###### 3. Re: Adding value of category to other category

No probs...this one should do the job

{COUNT(IIF([Type]='Other',1 ,NULL))}

Let me know if that doesn't do the trick

• ###### 4. Re: Adding value of category to other category

thanks it worked

• ###### 5. Re: Adding value of category to other category

i have another challenge here. how do i use this calculated field in LOD like below :

{FIXED DATEPART('month', [Bought Date]),DATEPART('week', [Bought Date]),[Country],[Category Group]:COUNTD([no of icecreams])}

• ###### 6. Re: Adding value of category to other category

What are you trying to calculate? and what level are you displaying it at?

What is COUNTD([no of icecreams])?

• ###### 7. Re: Adding value of category to other category

i need to show weekly number of icecreams bought.

i'm using countD to count distinct no of icecreams, like if same icecream number(unique code) is repeated thrice, it has to count as one instead of three.

• ###### 8. Re: Adding value of category to other category

So this isn't related to this?

{COUNT(IIF([Type]='Other',1 ,NULL))}

If not...

You can just bring your [Bought Date] into the Row/column shelf...If you drag it in with a "right button" press then you'll bring up the option to change the DATE level. Like this

By selecting Week, Tableau will aggregate the data to Calendar Week.

Then just bring in the Ice Cream field onto the opposing shelf. Again right button and you'll be given the Aggregation option. Select COUNTD

If you just drag stuff in with left button, you can click in each pill and change the aggregations

There shouldn't be a need to use FIXED LoDs. I only used them for the original question as we needed to "apply" the "other" sales to the other rows, and make it "resistant" to filtering.

Hope that helps, but if not, if you can post an example workbook it will be easier for me to solve your exact problem.