topic Re: How to simplify this expression? in QlikView

How to simplify this expression?

datanibbler — Thu, 22 Jan 2015 13:06:19 GMT

Hi,

in my Management_Dashboard, I have a nr. of "traffic_lights" (gauge_charts that can take the values of 1=red, 5=yellow and 9=green)

=> For every KPI that I want to display the status of, I load the current figures, compare them to the budget which I have in a variable,

and thus I compute the value.

Now, however, I face the following challenge:

I have two separate budgets for the same KPI this year, one for the one plant and another for the second plant.

The user of this Management_Dashboard can either select one or the other plant or not select at all, in which case they would see the sum of the two budgets

=> The formula is such that the "used" part of the budget (say, we have a yearly budget) is compared to the part of the year that has
already passed.

=> That makes for a formula with multiple nested IFs that will run over approx. one dozen lines or so since

- I have to find out

- whether the user has selected anything at all and

- which of the plants he has selected, if he has

=> Depending on that, the past part of the year will be compared to the spent part of

- the budget for plant_#1 or

- the budget for plant_#2 or

- the sum of both

Can anybody give me an idea of how to simplify this condition?

Thanks a lot!

Best regards,

DataNibbler

P.S.: Can I, like I can in any other type of diagram (say, in a straight_table), use one formula that I don't display to first calculate in a "side calculation" which budget is the relevant, based on the user's selections

and then just compare the used part of this to the past part of the month?

Can anybody g

Re: How to simplify this expression?

marcus_sommer — Thu, 22 Jan 2015 13:17:35 GMT

Hi DataNibbler,

I would rather use set analysis instead nested if-loops and check inside with getfieldselection() and/or getselectedcount() which budget is chosen and maybe set a default-value for it if more as one value is selected or possible.

- Marcus

Re: How to simplify this expression?

datanibbler — Thu, 22 Jan 2015 13:45:20 GMT

Hi Marcus,

how would you use set_analysis in this scenario?

I have already found out that I cannot use a "side calculation" like I can do in other types of charts because in a circular gauge like I have it, there is no way of creating an expression with no graphical representation.

P.S.: The formula in itself is not overly complicated - just a bit 😉 I have constructed it already, and tested the individual elements in text_boxes and I know what should be the result, and it seems to be correct.

My only remaining problem is the one of defining which of the two budgets (or the sum of both) should be considered, which makes for approx. 3 additional lines in the formula - which I need twice as well since the formula (in pseudo-code) looks like this

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

- IF (used part of monthly budget) <= (past part of the workdays of this month) => then return the value 9

- IF(used part of monthly budget) <= (((past part of the workdays of this month) * 1.05) => return the value 5

- otherwise return the value 1

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Re: How to simplify this expression?

marcus_sommer — Thu, 22 Jan 2015 14:10:47 GMT

Hi DataNibbler,

maybe something like this (without your year-matching):

sum({< BudgetType = {"$(=if(match(getselectedcount(BudgetType), 0, 2), 'plant_#1', // as default

getfieldselections(BudgetType))"}>} BudgetValue)

- Marcus

Re: How to simplify this expression?

datanibbler — Thu, 22 Jan 2015 14:17:42 GMT

Hi Marcus,

ok - I've decided that I probably can't really make the formula any easier (the set_expression you propose won't make it much easier or shorter), so instead I decided to split it up - I now have an expression_variable that contains the expression needed to define which budget is relevant based on the user's selections.

I then plug that expression_variable into the formula itself which shrinks that to three lines, like it was before.

I guess I'll leave it at that - it will be different next year (at the latest, maybe earlier), so it's no use spending too much time on it now 😉

Thanks for helping anyway!

Best regards,

DataNibbler