7 Replies Latest reply: Jul 18, 2011 1:34 PM by Erich Shiino

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

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!

• ###### Re: Easy question, but this newbie can't figure out syntax

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

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

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!

• ###### Re: Easy question, but this newbie can't figure out syntax

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

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

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?

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

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:

num(Date) as nDate .... etc

Then, the  expression will become:

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

Hope it helps,

Erich

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

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!

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

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