Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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!

7 Replies
MVP
MVP

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

Not applicable

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!

MVP
MVP

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

Not applicable

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?

erich_shiino
Honored Contributor

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:

Load...

num(Date) as nDate .... etc

Then, the  expression will become:

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

Hope it helps,

Erich

Not applicable

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!

erich_shiino
Honored Contributor

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