topic Re: Set analysis question in QlikView

Set analysis question

Anonymous — Sat, 07 Jan 2012 15:43:15 GMT

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

fheidenstecker — Sat, 07 Jan 2012 17:19:30 GMT

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

Set analysis question

Anonymous — Sat, 07 Jan 2012 17:59:39 GMT

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

Miguel_Angel_Baeyens — Sat, 07 Jan 2012 19:47:01 GMT

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