Easy question, but this newbie can't figure out syntax

[#1]

I need to calculate the sum of several measures (ex: Revenue) for the last 7 days, last 30 days, Month/Quarter/Year-to-Date....all based of of a MaxDate variable.  I'm new to QlikView and have been trying numerous syntax variations to no avail.   I'm sure the solution is easy, just not to me.  Can someone give me a sample or two to calculate the sums I mention above?

Thanks!

[Miguel_Angel_Baeyens]

Hi,

Try the following expressions:

Sum({< DateField = {">=$(=Date(vMaxDate -7))"} >} Revenue)
Sum({< DateField = {">=$(=AddMonths(Date(vMaxDate), -1))"} >} Revenue)

and so on.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

This is helpful.  Now I'm working on another report where the only date field brought back is "Date".  I tried using the 1st formula above for last 7 days, but since there is no vMaxDate variable I tried just using Date(max(Date) instead and it doesn't work.  Do you have any recommendations on how to get last 7 days when the only date field from the database is "Date"?

Thanks!

[Miguel_Angel_Baeyens]

Hi,

vMaxDate is a variable actually, not a field. Check this application in order to see some variables plus master calendar plus set analysis working all together.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

I've tried two different expressions, neither of which are working.  They are:

sum ({< Date = {'>=$(=Date(MaxDate - 7)) <=$(=Date(MaxDate))'}  Revenue)

sum ({< Date = {">=$(=Date(MaxDate - 7))"} >} Revenue)

Can you suggest why these expressions are not returning the expected result?

[erichshiino]

Hi,

Maybe set analysis is not evaluating date format properly.

I suggest, you create an auxiliary field in script which will be your date in a integer format.

The syntax (in script) will be:

Load...

num(Date) as nDate .... etc

Then, the  expression will become:

sum ({< nDate = {">=$(=num(Date(MaxDate - 7)))"} >} Revenue)

Hope it helps,

Erich

[#7]

Thanks, Erich, but I did as you describe and there is no summation being done.  When this expression is used with the Dimension I want to see, I'm still getting the grand total for each Dimension, not just the last 7 days.   Any other suggestions? 

Thanks again!

[erichshiino]

Is MaxDate one of your fields? or a variable?

If not, and you only have the fields Date and nDate, it would be:

sum ({< nDate = {">=$(=num(max(nDate))-7))"} >} Revenue)

or sum ({< nDate = {">=$(=num(max({1}nDate))-7))"} >} Revenue)

Hope it helps,

Erich