syntax set analysis

[#1]

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.

[sunny_talwar]

May be this:

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

[Gysbert_Wassenaar]

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.

[#4]

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.!

[#5]

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

[Kushal_Chawda]

try,

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

[sunny_talwar]

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