topic SV:Re: AW:Re: Value in between two variables in QlikView

Value in between two variables

Anonymous — Tue, 21 Sep 2010 14:33:33 GMT

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.

Value in between two variables

Tue, 21 Sep 2010 14:46:47 GMT

Something like this:

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

AW:Value in between two variables

Tue, 21 Sep 2010 14:50:00 GMT

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:)

AW:Value in between two variables

Tue, 21 Sep 2010 15:06:26 GMT

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


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

Value in between two variables

Anonymous — Wed, 22 Sep 2010 07:38:41 GMT

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 { } .......

AW:Re: Value in between two variables

Wed, 22 Sep 2010 09:31:46 GMT

Hi,

another Try:


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

I think you need the "$(="

Have Fun

Alex:)

AW:Re: Value in between two variables

Anonymous — Wed, 22 Sep 2010 10:12:32 GMT

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?

SV:Value in between two variables

gandalfgray — Wed, 22 Sep 2010 10:18:43 GMT

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.

SV:Value in between two variables

Anonymous — Wed, 22 Sep 2010 10:30:39 GMT

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.

SV:Re: AW:Re: Value in between two variables

gandalfgray — Wed, 22 Sep 2010 10:41:24 GMT

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)

AW:Re: SV:Value in between two variables

Wed, 22 Sep 2010 10:46:53 GMT

It was the qualification of the month field. omzet.month instead of month.

Have Fun Alex:)