Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Value in between two variables

I am trying to figure out how to work with variables, and I came to this formule which works:

Sum({$<Omzet.Month={$(=Month(Today(2))-1)}>} Omzet.Netto_orderbedrag)

It returns the sum of the "Omzet.Netto_orderbedrag" from the previous month (Month(Today(2))-1).

What I now want is is a formule which returns the "Omzet.Netto_orderbedrag" from several months, for example the 5 months before the previous month. So somewhere in the formule it must have something like this : >=(Month(Today(2))-6) >(Month(Today(2))-1)
But I don't get it to work, don't know which quote and breaks where to use.

I am sure you experts know how to do this! 🙂

Thanks.

1 Solution

Accepted Solutions
gandalfgray
Specialist II
Specialist II

Hi Dennisnet

The general problem to specify an interval using set syntax can be solved in this way:

<Field = {">=$(=vStart) <=$(=vStop)"}>

This specifies that Field should be larger than or equal to vStart and less then or equal to vStop.

In your case it would be

sum({$<Omzet.Month= {">=$(=Month(today(2))-6) <=$(=Month(today(2))-1)"}> Omzet.Netto_orderbedrag)

A sidenote: I think you may have to rework your formula because calculating with Month() as you do will get you into trouble when the months involved are in different years.

View solution in original post

10 Replies
Not applicable
Author

Something like this:

=SUM( {$< Month= {">=$(Month(Today(2))-6)<=$(Month(Today(2))-1)"}> } Omzet.Netto_orderbedrag)

Not applicable
Author

Hi Dennisnet,

I haven't tested it but I'm pretty shur that this mus work:

Sum({$<Omzet.Month={>="$(=Month(Today(2))-1)"}>} Omzet.Netto_orderbedrag)

Have Fun

Alex:)


Not applicable
Author

Sorry, the >= has to be placed in the quotes:


Sum({$<Omzet.Month={">=$(=Month(Today(2))-1)"}>} Omzet.Netto_orderbedrag)


Anonymous
Not applicable
Author

Thanks Érico.

Yes I thought so too, it must be someting like that, but this returns the complete Sum of [Omzet.Netto_orderbedrag] so it looks like it ignores everything between the { } .......

?

Not applicable
Author

Hi,

another Try:


=SUM( {$< Month= {">=$(=Month(Today(2))-6)<=$(=Month(Today(2))-1)"}> } Omzet.Netto_orderbedrag)


I think you need the "$(="

Have Fun

Alex:)

Anonymous
Not applicable
Author

Thanks Alexander,

I think your suggestion is right, but I think there must something wrong in my loading script which creates a different format.

Because I did some test and these where the results:

[Month] -- > sep //database field returns 3 caracters

Month(today) -- > sep //returns 3 caracters

Month(today)+1 -- > 10 //returns numbers (month number)

[Month]+1 -- > 10 //returns numbers (month number)

I don't think it is possible to calculate with these different formats right?

In my script I creat [Month] like this:

Orderdatum,
Month(Orderdatum) As Month ,



Should I do it different?

gandalfgray
Specialist II
Specialist II

Hi Dennisnet

The general problem to specify an interval using set syntax can be solved in this way:

<Field = {">=$(=vStart) <=$(=vStop)"}>

This specifies that Field should be larger than or equal to vStart and less then or equal to vStop.

In your case it would be

sum({$<Omzet.Month= {">=$(=Month(today(2))-6) <=$(=Month(today(2))-1)"}> Omzet.Netto_orderbedrag)

A sidenote: I think you may have to rework your formula because calculating with Month() as you do will get you into trouble when the months involved are in different years.

Anonymous
Not applicable
Author

Yes that works! Thanks Göran.

And you are right about the different years, I am going to think this one over.

But now at least I have a formula that works, so I can work from there.

Lots of Thanks to all of you.

gandalfgray
Specialist II
Specialist II

To solve the "year-problem" I would probably do something like

MonthStart(Orderdatum) As OrderMonth

This maps all dates in september 2010 to 2010-09-01, etc (the first day in the month)

Then you should use the addmonths() function.

I would also add a variable in the load script:

Let vCurrentMonth = MonthStart(today());

The final set expression using the created variable, would be something like this:

sum({$<Omzet.OrderMonth= {">=$(=AddMonths('$(vCurrentMonth)', -6) <=$(=AddMonths('$(vCurrentMonth)', -1)"}> Omzet.Netto_orderbedrag)