# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Contributor II

## 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

Tags (4)
3 Replies
Contributor II

## Set analysis question

Hi Henco,

have a look at the Set Analysis Wizard

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

It can help you generate the right statement!

Regards,

Fabian

Contributor II

## 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

MVP

## 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:LOAD ...     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