topic Re: Set Analysis in QlikView

Set Analysis

rido1421 — Tue, 03 Mar 2015 13:25:30 GMT

Hi There

I have an expression that needs to be limited to months that have passed.

vActualIndicator displays the current month

my expression needs to sum the balance up until the current period -vActualIndicator , at the moment my expression is rolling it up

and giving the sum of the the previous months aswell I know I need to use Min month or max month but cant seem to get it right

below is my expression.

Sum({<[Fiscal Month]={"<=$(vActualIndicator)"}>}Balance)

There are balances in the fiscal months after vActualIndicator I want it to ignore those as I need to use forecast figures for that.

Variables:

vActualIndicator=NUM(RIGHT(Max({1<FORECAST_INDICATOR={1}>}IndicatorFISCAL_YM),2))

vMINMTH

vMAXMTH

any help will be appreciated.

Regards,

Re: Set Analysis

Tue, 03 Mar 2015 18:03:39 GMT

Hi, try the next expresion:

Sum({$<={"<=[Fiscal Month]"}>}Balance)

Use the next page:

Set Analysis Wizard for QlikView | qlikblog.at

This is a wizard that help you to generate the set analysis that you need

Re: Set Analysis

JonnyPoole — Tue, 03 Mar 2015 18:07:19 GMT

Can you post a simple QVW ? the exact data values in the app are important when resolving these kinds of issues and a sample would provide that.

Re: Set Analysis

Oleg_Troyansky — Tue, 03 Mar 2015 20:36:15 GMT

Most likely, your issue is related to formatting of the Month field. Based on your formula, the variable vActualIndicator is a 2-digit numeric field. Your field [Fiscal Month] is likely formatted as a Month, i.e. a dual value. Set Analysis can't make selections if the values are not properly formatted. You can, however, formulate an advanced search condition to overcome the issue. Something like this:

Sum({<[Fiscal Month]={"=num([Fiscal Month])<=$(vActualIndicator)"}>}Balance)

Another issue to verify is the variable itself and how it's being expanded in the $-sign expansion. Place this expression in a Straigh Table and leave the Label empty. In the expression label, you will be able to see a fully rendered Set Analysis expression. What kind of a value do you see there?

best,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Re: Set Analysis

rido1421 — Wed, 04 Mar 2015 05:13:38 GMT

Hi Oleg

Fiscal Month is a number...

This is what my expression would look like if the variable were displayed

Sum({<[Fiscal Month]={"<=$(4)"}>}Balance)

what this will do is sum everything from period 1 to period 4 , what I need to do is sum only the month which I have selected, if I select multiple months then sum them all but only sum up until month 4, month 5 and 6 will have data but those months are not closed off as yet so I dont want to see them as actuals.

Re: Set Analysis

rido1421 — Wed, 04 Mar 2015 05:22:55 GMT

Hi Jonathan

Im finding it a bit difficult to create a sample file that will highlight my issue effectively Im using a UDR to generate a report so it will be a bit tricky to get the actual values

Re: Set Analysis

Oleg_Troyansky — Thu, 05 Mar 2015 15:52:14 GMT

Rido,

so, based on your explanation, you need to select an intersection between the user selections in the field Fiscal Month and the condition listed in your set analysis. In this case, use the operator *= instead of a simple =, like this:

Sum({<[Fiscal Month]*={"=num([Fiscal Month])<=$(vActualIndicator)"}>}Balance)

best,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Re: Set Analysis

rubenmarin — Thu, 05 Mar 2015 16:28:55 GMT

Hi Rido, maybe:

Sum({<[Fiscal Month]={"<=$(=vActualIndicator)"}>}Balance)