6 Replies Latest reply: Nov 5, 2015 7:49 AM by Sunny Talwar

# syntax set analysis

What i do wrong?

this sentence is correct and it works. It grabs all the data (1) instead of using the selection (\$), from all the facts matching groupID 3 and with an start time lower or equal to 01/11/2015 00:00:00 then it Sum it.

Sum({1<GroupID={'3'},StartTime={'<=01/11/2015 00:00:00'}>}Valor)

What i want to change is to make the 01/11/2015 00:00:00 automatic. i mean, put the current floor month start date. That way every new month i'll have the previous month data.

But i've tryed a lot of things and none seems to work.

Any Idea?

huge thanks.

• ###### Re: syntax set analysis

May be this:

Sum({1<GroupID={'3'},StartTime={"\$(='<=' & TimeStamp(MonthStart(Max(StartTime)), 'DD/MM/YYYY hh:mm:ss'))"}>}Valor)

• ###### Re: syntax set analysis

Sum({1<GroupID={'3'},StartTime={"\$(='<=' & TimeStamp(MonthStart(Max(StartTime)), 'DD/MM/YYYY hh:mm:ss'))"}>}Valor)

this worked, but i can't understand this {"\$(='<=' & TimeStamp(MonthStart(Max(StartTime)), 'DD/MM/YYYY hh:mm:ss'))"}>}

Could you please explain? it would help me so much.

Everything i put after " goes grey, so not sure what i'm doing.

huge thanks.!

• ###### Re: syntax set analysis

So I am equating Start time to this expression -> ='<=' & TimeStamp(MonthStart(Max(StartTime)), 'DD/MM/YYYY hh:mm:ss')

To know what that expression is, use it in a text box expression

='<=' & TimeStamp(MonthStart(Max(StartTime)), 'DD/MM/YYYY hh:mm:ss')

and see what the output is.

HTH

Best,

Sunny

• ###### Re: syntax set analysis

This should work if your StartTime field is a timestamp field:

Sum({1<GroupID={'3'},StartTime={"<=\$(=MonthStart(Today()))">}Valor)

If you're using a month field as dimension then this won't work as only values associated with the month value can be used to calculate a result for that month value.

• ###### Re: syntax set analysis

hi Gysbert! thanks for the anwer the solution you proposed didn't work, it output no result at all

• ###### Re: syntax set analysis

try,

Sum({1<GroupID={'3'},StartTime={"<=\$(=monthstart(max(StartTime)))"}>}Valor)