topic conditional set analysis in QlikView

conditional set analysis

Mon, 31 Oct 2011 13:15:03 GMT

I have a formula with a condition which works but is terrible ugly, i like to isolate the condition with a variable or another way.Ugly code:

if(DSS_DATE_FCST_MADE.MONTH_KEY<DSS_DATE_HISTORY.MONTH_KEY,

SUM({$<DSS_DATE.YEAR=,DSS_DATE.MONTH_NAME_SHORT=,DSS_DATE.DAY=,DSS_HISTORY_INVENTORY.DMD_HISTORY_GROUP={"DEMAND"},
DSS_DATE_HISTORY.MONTH_KEY={">=$(=DSS_DATE.MONTH_KEY-35)<=$(=DSS_DATE.MONTH_KEY+(1*$(vNrOfDmdMonth)))"} >}
DSS_HISTORY_INVENTORY.HIST_ACTUAL_DEMAND_QTY),

SUM({$<DSS_DATE.YEAR=,DSS_DATE.MONTH_NAME_SHORT=,DSS_DATE.DAY=,DSS_HISTORY_INVENTORY.DMD_HISTORY_GROUP={"FORECAST"},
DSS_DATE_HISTORY.MONTH_KEY={">=$(=DSS_DATE.MONTH_KEY-35)<=$(=DSS_DATE.MONTH_KEY+(1*$(vNrOfDmdMonth)))"} >}
DSS_HISTORY_INVENTORY.HIST_FORECAST_MGMT_QTY)

)

But I like to do it rather like this:

Assume I would find a way to set ta vriable vFactMode with the if condition and than I would use it in the set analysis, but I have no clue how to do:

SUM({$<DSS_DATE.YEAR=,DSS_DATE.MONTH_NAME_SHORT=,DSS_DATE.DAY=,DSS_HISTORY_INVENTORY.DMD_HISTORY_GROUP={=$(vFactMode)},
DSS_DATE_HISTORY.MONTH_KEY={">=$(=DSS_DATE.MONTH_KEY-35)<=$(=DSS_DATE.MONTH_KEY+(1*$(vNrOfDmdMonth)))"} >}
DSS_HISTORY_INVENTORY.HIST_ACTUAL_DEMAND_QTY)

conditional set analysis

swuehl — Mon, 31 Oct 2011 23:34:07 GMT

uweh,

have you tried setting the variable vFactMode to

=if(DSS_DATE_FCST_MADE.MONTH_KEY<DSS_DATE_HISTORY.MONTH_KEY,'DEMAND','FORECAST')

and then as you suggested:

SUM({$<DSS_DATE.YEAR=,DSS_DATE.MONTH_NAME_SHORT=,DSS_DATE.DAY=,DSS_HISTORY_INVENTORY.DMD_HISTORY_GROUP={$(vFactMode)},

DSS_DATE_HISTORY.MONTH_KEY={">=$(=DSS_DATE.MONTH_KEY-35)<=$(=DSS_DATE.MONTH_KEY+(1*$(vNrOfDmdMonth)))"} >}

DSS_HISTORY_INVENTORY.HIST_ACTUAL_DEMAND_QTY)

(I would not use an equal sign before the dollar sign expansion here).

Regards,

Stefan

conditional set analysis

Tue, 01 Nov 2011 08:38:51 GMT

Stefan, it does not work, but maybe I don't understand the implemenation.If i set the variable in the same expression before the calculation, that does not work and I thing I can only have one expression:

=$(vFactMode) = if(DSS_DATE_FCST_MADE.MONTH_KEY<DSS_DATE_HISTORY.MONTH_KEY, 'DEMAND','FORECAST')
=SUM({$<DSS_DATE.YEAR=,DSS_DATE.MONTH_NAME_SHORT=,

DSS_DATE.DAY=,

DSS_HISTORY_INVENTORY.DMD_HISTORY_GROUP={$(vFactMode) },
DSS_DATE_HISTORY.MONTH_KEY={">=$(=DSS_DATE.MONTH_KEY-35)<=$(=DSS_DATE.MONTH_KEY+(1*$(vNrOfDmdMonth)))"} >}
DSS_HISTORY_INVENTORY.HIST_ACTUAL_DEMAND_QTY)

And if I set as a variable it also seems not to work

Variable name: vFactMode and Expression:

= if(DSS_DATE_FCST_MADE.MONTH_KEY<DSS_DATE_HISTORY.MONTH_KEY, 'DEMAND','FORECAST')

Do I rellay to have this ugly code? is there no way to shorten this?

conditional set analysis

swuehl — Tue, 01 Nov 2011 11:06:49 GMT

Sorry, I didn't want to say that you should define your variable in front of and within the object expression (like in your first part). That is not going to work. You need to define a variable in Settings - variable overview (or in the script).

What is the context of your expression? Do you use the two MONTH_KEYs of your condition as dimensions in a chart, where you use this expression?

Probably yes...

Hm, then you need the evaluation of the variable to happen not before evaluating the expression in the chart context.

Haven't tried yet, but maybe try to change the variable definition to

if(DSS_DATE_FCST_MADE.MONTH_KEY<DSS_DATE_HISTORY.MONTH_KEY, 'DEMAND','FORECAST')

(removing the equal sign).

And then (just for now), create a new expression in your chart and put in

$(=vFactMode)

$(=$(vFactMode))

Anyway, you need to use a variable that evaluates to the correct output in your expression context then.

(the correct output should be either 'FORECAST' or 'DEMAND', right?)

Then you need to put this expression in your original expression as set modifier between the curly brackets (maybe adding single quotation marks, but I don't think you need them).

Hope this helps,

Stefan