Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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.

Tags (1)
6 Replies
MVP
MVP

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

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.


talk is cheap, supply exceeds demand
Not applicable

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

Not applicable

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)

MVP
MVP

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