5 Replies Latest reply on Jul 2, 2017 6:53 AM by Zhouyi Zhang

LOD Count & YTD formula

I have created a formula which counts distinct number of items we sold. We have at invoice number some 0 which was creating a count , I was able to exclude these and the following formula works well :

{ include [cust id],[sku]: IF sum([delv])=0 THEN 0 ELSE COUNTD( sku]) END}

Now I am trying to make this work for YTD - the following works but does not exclude the 0s and I am struggling to include IF sum([delv])=0 THEN 0

{ include [cust id],[sku]: COUNTD( IF [CY YTD] THEN [sku] END)}

As a newbie any gidance would be appreciated.

Thanks

MD

• 1. Re: LOD Count & YTD formula

Hi, Michael

have you tried

{ include [cust id],[sku]: COUNTD( IF [CY YTD] and [delv] <> 0 THEN [sku] END)}

ZZ

• 2. Re: LOD Count & YTD formula

Thanks ZZ !

No I have not tried it and Yes that worked for me .

But not 100% because of LOD thought.

I have a a follow up question - I am not sure should open a new ticket.

I have modified the formula to be >0 rather then <> to suit our needs.

{ include [cust id],[sku]: COUNTD( IF [CY YTD] and [delv] > 0 THEN [sku] END)}

Each invoice has a code for sale and /or pickup - so we can sell 1 and pickup -1 = net 0

My new YTD formula picks this counts this as  1 - since we did initially sell it .

The original formula without the YTD component excludes this as I have the sum included

IF sum([delv])=0 THEN 0 ELSE COUNTD( sku]) END}

I can not include Sum into the new YTD formula (better said don't know how) due to aggregates.

Thanks again

• 3. Re: LOD Count & YTD formula

Hi, Michael

I am not quite getting what you described. If there is a sample workbook and tell me what's your expected value would be helpful

ZZ

• 4. Re: LOD Count & YTD formula

I will try to explain with example :

1/ We sell a item to a customer which creates invoice - no issue here.

2/ When we return we find that the quality of the product has diminished so we replace the product

Invoice contains the following descriptions, I am using + to separate the fields

In summary we a line for Sale as +1 and Pickup -1 The net QTY sold is 0.00

Inv No + SKU      +  Inv. Action  +  QTY

123     + Item1     + Sale            +    1

123     + Item1     + Pickup         +   (1)

The following formula shows the correct amount = 0

POD= { INCLUDE  [Cust Id],[SD sku]: IF sum([QTY])>0 THEN COUNTD([SD sku]) ELSE 0 END}

I believe that  this is due to the inclusion of sum([delv])

3/ The following formula shows up as 1

POD YTD ={ INCLUDE  [Cust Id],[SD sku]: COUNTD(if [CY YTD] and [QTY]> 0 THEN [SD sku]END)}

When replying I was unable to attache workbook , I thus open a new topic - Count & LOD

Hopefully that helps

• 5. Re: LOD Count & YTD formula

Hi, Michael

Sorry about the late response, and thanks for your detailed explanation. It is much more clear to me.

Please find my solution below to your question, hope this could help.

Workbook attached for your reference. Please let me know if any further question

ZZ