1 2 Previous Next 26 Replies Latest reply on May 9, 2017 8:56 AM by Okechukwu Ossai

# Calculations not working properly -- LOD possibility

Hello,

I am attaching a set of data exported from Tableau which includes 2 calculated columns. I have also listed the calculations for these fields in columns H and I.

I am trying to create 2 calculations that do the following:

1. When a product ID has "1P sales" > 0 (Column B) and also has "3P/PVL Sales" (Column E)... List that amount within "3P Sales on 1P items" (Column C)

2. When a product ID has "1P sales" <= 0 (Column B) and also has "3P/PVL Sales" (Column E)... List that amount within "3P Sales on 1P items \$0" (Column D)

The formula seems to work for some of the items but not all. The goal would not be to display it as a table but within a bar chart for each column.

Please let me know if this is a candidate for an LOD expression which I have tried with no success.

Best,

D

• ###### 1. Re: Calculations not working properly -- LOD possibility

Hi Dan,

Not sure if I understand your challenge correctly by find my "drawings" as reference below and stored in attached workbook version 9.3 located in the original thread.

1.zn([1P Sales]): zn([1P Sales]) getting all entries with a blank notated as 0

2. 3P Sales on 1P items LOD: if {fixed[Product ID]:sum([zn([1P Sales]])])}>0 then [3P / PVL Sales] else 0 END (based on LOD: in this scenario not valid while Product ID is still used in the view)

3. 3P Sales on 1P items: 0 LOD  if {fixed[Product ID]:sum([zn([1P Sales]])])}<=0 then [3P / PVL Sales] else 0 END(based on LOD: in this scenario not valid while Product ID is still used in the view)

4. 3P Sales on 1P items: if sum([zn([1P Sales]])>0 then [3P / PVL Sales] else 0 END

5. 3P Sales on 1P items: if sum([zn([1P Sales]])<=0 then [3P / PVL Sales] else 0 END

Regards,Norbert

• ###### 2. Re: Calculations not working properly -- LOD possibility

Hi Dan,

I assumed that your data structure is exactly like the Excel spreadsheet you attached. If yes, then try the following;

[3P Sales on 1P items]

IF NOT ISNULL([3P / PVL Sales]) AND [1P Sales] > 0 THEN [3P / PVL Sales] END

[3P Sales on 1P items \$0]

IF NOT ISNULL([3P / PVL Sales]) AND [1P Sales] <= 0 THEN [3P / PVL Sales] END

Hope this helps.

Ossai

• ###### 3. Re: Calculations not working properly -- LOD possibility

Hi Ossai,

This didn't work for me.

The data that I have within my master file is only for the columns "1P sales" and "3P / PVL Sales". The other columns highlighted in yellow were what was produced through my current calculations within Tableau.

For example, row 72 for item "123-CA-B017MLEPUK1121PVL" has \$638.55 in 1P Sales. It also has \$602 in 3P / PVL sales. The 3P figure of \$602 should be sitting in Column C for "'3P Sales on 1P Items" and not within Column D "'3P Sales on 1P Items \$0".

I hope that better explains the issue.

• ###### 4. Re: Calculations not working properly -- LOD possibility

Hi Dan

Please see the attached and below

1p>0 =     IF zn([1P Sales])>0 and zn([3P / PVL Sales]) > 0 then [3P / PVL Sales] else 0 END

1p<=0      IF zn([1P Sales])<=0 and zn([3P / PVL Sales]) > 0 then [3P / PVL Sales] else 0 END

will yield

Let me know if this helps

Jim

• ###### 5. Re: Calculations not working properly -- LOD possibility

Hi Dan,

The updated formula works. See image below

Refresh your browser, copy the codes and try them again.

Hope this helps.

Ossai

• ###### 6. Re: Calculations not working properly -- LOD possibility

Hi, Dan

Attached is a workbook I created based on your data, not sure if it is what you want.

ZZ

• ###### 7. Re: Calculations not working properly -- LOD possibility

I am currently not working in 10.2 can you show the calculations.

Unfortunately, the other answers are not quite working out as I am still spotting some errors. I think it is due to my raw data only containing "1P Sales" and "3P/PVL Sales" and not the other items.

• ###### 8. Re: Calculations not working properly -- LOD possibility

Hi Dan,

Did you try this?

[3P Sales on 1P items]

IF NOT ISNULL([3P / PVL Sales]) AND [1P Sales] > 0 THEN [3P / PVL Sales] END

[3P Sales on 1P items \$0]

IF NOT ISNULL([3P / PVL Sales]) AND [1P Sales] <= 0 THEN [3P / PVL Sales] END

It works on my end. Is the bar chart below what you are expecting? Let me know if you spot any inconsistency and I can look into that for you.

Hope this helps.

Ossai

• ###### 9. Re: Calculations not working properly -- LOD possibility

My calculation is almost the same as Ossai.

ZZ

• ###### 10. Re: Calculations not working properly -- LOD possibility

Can you try replicating using the attached file? I just removed the calculated items and left the raw data. When using the calculations I am still spotting errors within my file.

• ###### 11. Re: Calculations not working properly -- LOD possibility

Hi Dan,

See attached workbook. It will be helpful if you can say what the errors are and where or how they occur. Maybe including the part of your data causing the error in the sample data will be helpful also.

Ossai

• ###### 12. Re: Calculations not working properly -- LOD possibility

You can see that I didn't include 3P Sales when 1P Sales is null.

For example, Product IDs 123-CA-B00ABRJQNO2840PVL and 123-CA-B00ACN0O1A6695PVL. Do you want to include these kind of sales data?

• ###### 13. Re: Calculations not working properly -- LOD possibility

I guess that is another issue as well. So all of these items are considered

1P regardless of \$0 in sales or null. Technically the 1P column should have

a \$0 if there is no sales there. I think the data needs to be cleaned up as

well which may eliminate the need to use the NULL.

On Fri, May 5, 2017 at 4:06 PM, Okechukwu Ossai <

• ###### 14. Re: Calculations not working properly -- LOD possibility

Hi Dan,

Oh I see, this makes things even more straightforward. I was wondering why Jim and Norbert's solution didn't work for you the first time because they seemed to have accounted for the Null. In any case, here is even a simpler formula that seems to do the job. Let me know if you are still spotting any errors.

[3P Sales on 1P items]

IF ZN([1P Sales]) > 0 THEN [3P / PVL Sales] END

[3P Sales on 1P items \$0]

IF ZN([1P Sales]) <= 0 THEN [3P / PVL Sales] END

See attached workbook.

Ossai

1 2 Previous Next