7 Replies Latest reply on Sep 19, 2018 9:12 AM by Jim Dehner

How to calculate WTD, MTD when we have  aggregate and Non- aggregate functions

Hi ...

I am trying to calculate WTD as below but it fails as "cannot mix aggregate and Non- aggregate comparison or results in 'if' expression"

WTD: If DATEDIFF('week',[ Date1],TODAY())=0 and [Date1]<= TODAY() THEN [OTP] END

Where as OTP is other calculated field

• 1. Re: How to calculate WTD, MTD when we have  aggregate and Non- aggregate functions

Hi Suman,

change the calculation like below

If ATTR(DATEDIFF('week',[ Date1],TODAY()))=0 and ATTR([Date1])<= ATTR(TODAY()) THEN [OTP] END

Hope this helps

BR,

NB

• 2. Re: How to calculate WTD, MTD when we have  aggregate and Non- aggregate functions

Hi

the message you are getting is that OTP us aggregated but Date1 is not wrap date 1 in MIN()

like Min(date1)

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• 3. Re: How to calculate WTD, MTD when we have  aggregate and Non- aggregate functions

Hi All,

I used below query to calculate WTD, MTD and YTD

MTD:ZN(IF MIN(DATEDIFF('month',[ Date],TODAY()))=0 and MIN([ Date])<= (TODAY()) THEN ([OT (%)]) END)

YTD: ZN(IF MIN(DATEDIFF('year',[ Date],TODAY()))=0 and MIN([ Date])<= (TODAY()) THEN ([OT (%)]) END)

OT (%) is calculated field

Now I get same value for MTD and YTD

In other sheet when I drag Date and OT (%)  and filter date to this year then value of OTS is totally different from above YTD query value...

Thanks

• 4. Re: How to calculate WTD, MTD when we have  aggregate and Non- aggregate functions

thanks

JIm

• 5. Re: How to calculate WTD, MTD when we have  aggregate and Non- aggregate functions

Hi..

Attached file

• 6. Re: How to calculate WTD, MTD when we have  aggregate and Non- aggregate functions

Hi Jim,

Can you help me on issue

Thanks

• 7. Re: How to calculate WTD, MTD when we have  aggregate and Non- aggregate functions

you just sent a file with data - it does not include any of your calculated fields