1

I am trying to calculate the percent different in net sales between current period and prior period. I am able to calculate the percent difference for a measure that is not aggregated. However, in my case, net sales is calculated based on the below formula which makes it an aggregate measure:

SUM([Gross Sales])-ZN(SUM([Promotions]))-ZN(SUM([Refund_Amount]))-ZN(SUM([OrderTax]))-ZN(SUM([Shipping Tax]))

Below are the other formulae I am using

Prior period sales: IF ATTR([In Prior Period?]) THEN [Net Sales] END

Current period sales: IF ATTR([In Current Period?]) THEN [Net Sales] END

% Diff Net Sales: ([Current Period Sales]-[Prior Period Sales]) /[Prior Period Sales]

However, I am not getting any values for % Diff Net Sales. However, when using a non-aggregated measure, saying Unit Sales instead of Net Sales, I am getting the correct value. In this case, I use the formula:

Current period sales: SUM(IF [In Current Period?] = True THEN [Unit Sales] END)

These are the calculations and data structure I am using.(Fields with a preceding # are calculated fields and hence aggregated fields) data structure

I appreciate any help with this issue. Gowri

I tried the below formula for current period sales:

(Refunds are obtained from a blended data source, hence slightly different)

SUM(if [In Current Period?] then [Gross Sales] END)
-ZN(SUM(if [In Current Period?] then [Promotions] END))
-ZN((if attr([In Current Period?]) then SUM([Adjustments].[Refunds]) END))
-ZN(SUM(if [In Current Period?] then [OrderTax] END))
-ZN(SUM(if [In Current Period?] then [Shipping Tax] END))

I am able to get a value for % Diff Net Sales. However, the value seems to be wrong. This seems to be because of a difference in the Current Period Sales and Prior Period Sales value as shown in images below. In one sheet, I am getting correct values, but in another, wrong values.(I have selected January 2021 as the current period using a parameter) These are the correct values. Correct Values These are the wrong values and hence getting a wrong % Diff Net Sales value. Wrong Values

What could be the reason for this and how do I resolve it please?

enter image description here

Gowri Mohan
  • 11
  • 4

1 Answers1

1

Bury the date into the Net Sales calcs.

SUM(if [In Current Period?] then [Gross Sales] END)
-ZN(SUM(if [In Current Period?] then [Promotions] END))
-ZN(SUM(if [In Current Period?] then [Refund_Amount] END))
-ZN(SUM(if [In Current Period?] then [OrderTax] END))
-ZN(SUM(if [In Current Period?] then [Shipping Tax] END))

and

SUM(if [In Prior Period?] then [Gross Sales] END)
-ZN(SUM(if [In Prior Period?] then [Promotions] END))
-ZN(SUM(if [In Prior Period?] then [Refund_Amount] END))
-ZN(SUM(if [In Prior Period?] then [OrderTax] END))
-ZN(SUM(if [In Prior Period?] then [Shipping Tax] END))

Then this will work.

% Diff Net Sales: ([Current Period Sales]-[Prior Period Sales]) /[Prior Period Sales]

Bernardo
  • 3,212
  • 1
  • 10
  • 17
  • Hi Bernardo. Thank you so much for the suggestion. I've made great progress based on your help. However, the values don't seem to be accurate. There's a difference in the Current/Prior period sales value between two sheets(with and without date dimension). I have edited my question with details and pictures. It would be great if you can have a look and suggest how I can fix this. Really appreciate your help so far. – Gowri Mohan Feb 24 '21 at 11:36
  • Without sample data I'm afraid I cannot help much. But I will say is check your logic for current / prior periods. They appear to capture more than just Jan 2021 and Dec 2020 respectively. – Bernardo Feb 24 '21 at 12:20
  • Hi Bernardo. Is there a way I can send you a .twbx with dummy data but the same issue replicated? Would you be able to help me out then please? I've been struggling with this for over a week now. – Gowri Mohan Feb 24 '21 at 13:21
  • 1
    Post it to dropbox/googledrive/or similar and paste the link here – Bernardo Feb 24 '21 at 13:44
  • Hi Bernardo. I looked into the calculations and figured it was an issue of refunds not being considered when displaying the current/prior period sales without the OrderDate dimension. If you check the last picture in my question, I've highlighted this issue. Could this be because of an issue with the data blending between the two sources or should I frame the calculation differently or something? How can I resolve this? Kindly have a look. – Gowri Mohan Feb 24 '21 at 13:45
  • Here is the link if needed. Thank you so much for your help. https://drive.google.com/file/d/1hUHhCcF0PbfWSTp8Aivkwmw7VDL4h_Ex/view?usp=sharing – Gowri Mohan Feb 24 '21 at 13:50
  • Ah, I didn't realize you're data blending. Your join field needs to be in the view for the blending to work, which is why the Correct Data tab is right because you have date in columns. is there anyway to combine the data so it's one source, through Tableau Prep or something? – Bernardo Feb 24 '21 at 14:00
  • I'm not sure if joining will work because I want the refunds to be handled in the period they were created and not when that order was created. It might become complicated handling two different dates in the same datasource right? Is there a work around please? – Gowri Mohan Feb 24 '21 at 14:13
  • 1
    The refunds have their own date so if you stack the data, it'll align with when they were created, not when the order was made. I'm sorry but I don't have a work around. Data blending has its downside, I try to avoid it where possible. – Bernardo Feb 24 '21 at 14:18
  • I agree with @Bernardo here – AnilGoyal Feb 24 '21 at 14:21
  • Hi Bernardo, by stack do you mean creating a union between the orders table and the refunds table? – Gowri Mohan Feb 24 '21 at 14:30
  • Yes. The the dates will all be in one column. – Bernardo Feb 24 '21 at 14:40
  • Alright. I'll see what I can do. Thank you for your help so far. – Gowri Mohan Feb 24 '21 at 16:01