3 Replies Latest reply: Jan 7, 2012 2:47 PM by Miguel Angel Baeyens de Arce

# Set analysis question

Hi all,

At this moment I use the following expression: sum({1}if(MonthYear=num(Month(Today())) &'-'& Year(Today()), Transamount)).

This gives me the oppurtinity to show the revenue for the current month. The problem is that it gives me to much, so I want to put this in a set analysis.

Can anyone give me the right expression?

Kind regards,

Henco

• ###### Set analysis question

Hi Henco,

have a look at the Set Analysis Wizard

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx

Regards,

Fabian

• ###### Set analysis question

Hi Fabian, thats a great tool but unfortunately I can't get it done. When I fill I get this expression: Sum({1<Jaar={'Year(Today())'},Maand={'num(month(today())'}>}Transamount) but that one doesnt work. Can you help me with this specific set analysis? Kind regards, Henco

• ###### Re: Set analysis question

Hi Henco,

Use a date field (that in QlikView is numeric) instead of a year month field (that is a string, and as such takes longer to process) in the set analysis expression:

```Sum({1< DateField = {">=\$(=MonthStart(Today()))<=\$(=MonthEnd(Today()))"} >} Transamount)
```

That will return the sum of values in Transamount where DateField (your date field) is between the start and end of current month. But even faster is to create a flag field in your script, so the current month is always identified, something like

```CalendarTable:
If(InMonth(DateFieldorExpressionHere, Today()), 1, 0) AS CurrentMonthFlag,
... // rest of the script here
```

So the expression is simplified to

```Sum({1< CurrentMonthFlag = {1} >} Transamount)
```

Hope that helps.

Miguel