12 Replies Latest reply on Jul 12, 2016 5:58 AM by Xianan Zhao

# Conditional Field

Hi guys,

First thank you for your time to read my post. This question bugs me for awhile. I would like to add a conditional field on the workbook depending on the sales number by month.

Here are the conditions,

if a customer's sales in July(current month) is zero or null, then it shows 'Has Not Bought'.

If a customer's sales in July(current month) is less than the sales in June (previous month), then it shows 'Down 1 Month'.

If a customer's sales in July(current month) is less than June and May (previous 2 months), then it shows 'Down 2 months'.

Else show 'Good'

It may look like this Excel:

See attachment for sample workbook

Thanks for help,

Sherman

• ###### 1. Re: Conditional Field

Hi Sherman

First we need this month sales

>> IF MONTH([InvoiceDate]) = MONTH(TODAY()) THEN [GrossSales] ELSE NULL END

if a customer's sales in July(current month) is zero or null, then it shows 'Has Not Bought'.

This only deals with the null and zeros, what do you want if they have bought?!

IF ISNULL(SUM([This Month Sales]))

OR SUM([This Month Sales]) = 0

THEN 'Has not bought'

ELSE NULL               << what if they have bought?!

END

Will look at the more difficult stuff...

Cheers

Mark

1 of 1 people found this helpful
• ###### 2. Re: Conditional Field

Thanks for help Mark. If they have bought, then the conditional field will display 'Good'

• ###### 3. Re: Conditional Field

Apologies, you did say in your question!

I wont finish it now, but I'll come back to you

• ###### 4. Re: Conditional Field

No problem. Thank you, Mark.

• ###### 5. Re: Conditional Field

We can do like this also...

July

IF DATEDIFF('month',[InvoiceDate],TODAY())=0 then [GrossSales] END

June

IF DATEDIFF('month',[InvoiceDate],TODAY())=1 then [GrossSales] END

May

IF DATEDIFF('month',[InvoiceDate],TODAY())=2 then [GrossSales] END

• ###### 6. Re: Conditional Field

If a customer's sales in July(current month) is less than June and May (previous 2 months), then it shows 'Down 2 months'.

Do you mean

July > May + June (added together)

Or

July > May and July > June (bigger than the sales of either month)

Or something else?

I got to go... so far we have...

This Month

IF DATEDIFF('month',[InvoiceDate],TODAY())=0 then [GrossSales] END

Last Month

IF DATEDIFF('month',[InvoiceDate],TODAY())=1 then [GrossSales] END

Back 1 month test

SUM([Last Month Sales]) > SUM([This Month Sales])

Final

IF ISNULL(SUM([This Month Sales]))

OR SUM([This Month Sales]) = 0 THEN 'Has not bought'

ELSEIF [Back 1 Month Test] = TRUE THEN 'Down 1 Month'

ELSEIF.... TO DO

ELSE 'Good'

END

Will pick it up tomorrow, hope that's clear so far

Cheers

Mark

1 of 1 people found this helpful
• ###### 7. Re: Conditional Field

Here is one of possible solutions attached.

1 of 1 people found this helpful
• ###### 8. Re: Conditional Field

Thank you Mark. Regarding to your question, it is smaller than the sales of either month. If July < June AND July < May, then 'Down 2 Months'

• ###### 9. Re: Conditional Field

Thank you very much Lukasz. This works great! I don't know much about the Window Calculation and LOD expression. Thanks for the sample. I will keep learning.

• ###### 10. Re: Conditional Field

It seems there is a little problem with 'Down 2 Months' condition. I will try to fix that. Thanks.

• ###### 11. Re: Conditional Field

Then, you should amend the condition and its order as follows:

1 of 1 people found this helpful
• ###### 12. Re: Conditional Field

Thanks for the follow up. I did the exactly the same thing.