11 Replies Latest reply: May 17, 2018 8:55 AM by Thomas Williams

# Date Function

Hi, can anyone please confirm why my formula won't work, I think it's to do with the date?

It worked using this formula

Alt(sum({<[Country] = {'USA'}>}if(left(Period,4)='2018' and match(Lookup,'10tata'),Val,0))

but now I want to change the date to YTD and no longer works

Alt(sum({<[Country] = {'USA'}>} and (Period={"\$(='>=201801<=2018' & Num(Month(Today()), '00'))"} and match(Lookup,'10tata'),Val,0))

• ###### Re: Date Function

EDITED

depending on the format of your date field "Period", the syntax should be something like this inside the set analysis

Period= {">=201801<=201805"}

or something dynamic:

Period={">=\$(=Date(YearStart(Max(Period)),'YYYYMM'))<=\$(=Date(Max(Period),'YYYYMM')))"}

final expression:

Alt(sum({<[Country] = {'USA'},Period={">=\$(=Date(YearStart(Max(Period)),'YYYYMM'))<=\$(=Date(Max(Period),'YYYYMM')))"},Lookup={'10tata'}>} Val),0)

• ###### Re: Date Function

That doesn't work. My date format is YYYYMM.

The syntax looks right but it doesn't return any results

• ###### Re: Date Function

I was changing the expression, did you take a look at the final one ?

• ###### Re: Date Function

Yes thank you, sorry I answered too quickly, that works in isolation. How would I look to now do this in a sum please like below, I have replicated the logic but it doesn't work

Alt(sum({<[Country] = {'USA'},Period={">=\$(=Date(YearStart(Max(Period)),'YYYYMM'))<=\$(=Date(Max(Period),'YYYYMM')))"},Lookup={'10tata'}>} Val),0)

/

above(sum({<[Country] = {'USA'},Period={">=\$(=Date(YearStart(Max(Period)),'YYYYMM'))<=\$(=Date(Max(Period),'YYYYMM')))"},Lookup=['10tata'),Val,0)))-1,0)

• ###### Re: Date Function

what are you trying to do exactly with this expression ?

Val of the month / Val of the previous month ?

• ###### Re: Date Function

Im looking at current month and a cumulative growth. It works fine on the old formula but want to have it automated

• ###### Re: Date Function

Can you please take the working formula, put it on a table using PERIOD as dimension and show us the expected output ?

Thanks

• ###### Re: Date Function

Se essentially what I am trying to achieve is taking this logic below

if(sum({<Period={"2018*"}, [Country] = {'USA'},[Sheet1.Dashboard Name]={'TA'}>}[VAL]),

sum({<Period={"2018*"}, [Country] = {'USA'},[Sheet1.Dashboard Name]={'TA'}>}[VAL]))

and applying it onto this

Alt(sum({<[Country] = {'UK'}>}if(left(Period,4)='2018' and match(Lookup,'10tata'),Val,0))

/

above(sum({<[Country] = {'UK'}>}if(left(Period,4)='2018' and match(Lookup,'10tata'),Val,0)))-1,0)

The date can stay hardcoded for now if easier.

• ###### Re: Date Function

where is the test of the IF here:

if(sum({<Period={"2018*"}, [Country] = {'USA'},[Sheet1.Dashboard Name]={'TA'}>}[VAL]),

sum({<Period={"2018*"}, [Country] = {'USA'},[Sheet1.Dashboard Name]={'TA'}>}[VAL]))

• ###### Re: Date Function

what do you mean, where is the test? I have applied this onto my line chart logic and it does exactly what I need it to do so need to apply this to this forumula but can't get the syntax right

• ###### Re: Date Function

on this expression:

if(sum({<Period={"2018*"}, [Country] = {'USA'},[Sheet1.Dashboard Name]={'TA'}>}[VAL]),

sum({<Period={"2018*"}, [Country] = {'USA'},[Sheet1.Dashboard Name]={'TA'}>}[VAL]))

where is the test? the comparison ?

if(condition , then , else)

• ###### Re: Date Function

This logic removes 0 values from within my line chart so I need to do this for my the formula listed above