Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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