Change key figure (measure) in a dimension

[ck95]

Hello dear Qlik community,

I have the following concern and hope theat one of you can help me.

Briefly summarized the topic. I've created a reorder report. This reorder dashboard enables me to figure out which items should be reordered.

Part of the table with the dimensions and key figures can be seen in the picture with examples.

The reorder decision was determined using IF statements and key figures in Set Analysis and appears to be one dimension. (The finished dashboard consists of several QVDs in the data editor and is therefore very complex and the calculation can only be carried out in the Set Analysis.) However, it is still a key figure. And that's the problem. I need the reorder decision as a dimension. My plan is to create a filter and can only see reorder decisions that have the value "yes". How should I do that?

[stevejoyce]

what's a "key figure"?

Can you create Reorder decision in your load script?  Then you can either limit data to "yes" in your data set, or add a filter object, or use in set analysis.

[ck95]

A key figure is also called a measure in the English version of Qlik Sense. So a value with which you can perform calculations. 🙂

For me it isn't possible to make the reorder decision in the load script.
In the data editor I upload different QVDs that all have different database connections. I only have the connections of all required data sets when I have loaded the data and open the worksheet for using Set Analysis to create my reorder decision.

[stevejoyce]

Gotcha.

For a calculated dimension, add aggr( <measure/condition> , <field to aggr by>). 

If you can't figure it out with above, can you send an example of your expression and dimensions.

[ck95]

Sorry for my late response, but I hope  you can help me.

IF(

IF(
((if(
(ceil(date(today())-date(ctime)) < '21' and Sum(quantityAvailableOms) = '0'),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/ceil(date(ctime)-(today()-21)),
if(
min(datum) > date(today()-21) and (today()-min(datum)) < '7',
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/7,
if(
min(datum) > date(today()-21),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/(today()-min(datum)),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/21
))
))>'0,04')
and

(
Sum(quantityAvailableOms)/
((if(
(ceil(date(today())-date(ctime)) < '21' and Sum(quantityAvailableOms) = '0'),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/ceil(date(ctime)-(today()-21)),
if(
min(datum) > date(today()-21) and (today()-min(datum)) < '7',
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/7,
if(
min(datum) > date(today()-21),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/(today()-min(datum)),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/21
))
))) < $(v_Tage)
)

and

Sum([marge_GMO])>'0'

and

Sum([Brand_VC2])>'0',

CEIL(
if(

((
CEIL((if(
(ceil(date(today())-date(ctime)) < '21' and Sum(quantityAvailableOms) = '0'),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/ceil(date(ctime)-(today()-21)),
if(
min(datum) > date(today()-21) and (today()-min(datum)) < '7',
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/7,
if(
min(datum) > date(today()-21),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/(today()-min(datum)),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/21
))
))*$(v_Tage))
* (1-(if(
min(datum) > date(today()-21), '0,40',
Sum({1<[type]={"retoure"}>}[menge])
/
Sum({1<[type]={"sale"}>}[menge])
)))
) - Sum(quantityAvailableOms)-Sum(quantityAdvisedWms)) < '0', '0',

((
CEIL((if(
(ceil(date(today())-date(ctime)) < '21' and Sum(quantityAvailableOms) = '0'),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/ceil(date(ctime)-(today()-21)),
if(
min(datum) > date(today()-21) and (today()-min(datum)) < '7',
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/7,
if(
min(datum) > date(today()-21),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/(today()-min(datum)),
Sum({1<[type]={"sale"},[datum]={">=$(=date(today()-21,'YYYY-MM-DD'))"}>}[menge])/21
))
))*$(v_Tage))
* (1-(if(
min(datum) > date(today()-21), '0,40',
Sum({1<[type]={"retoure"}>}[menge])
/
Sum({1<[type]={"sale"}>}[menge])
)))
) - Sum(quantityAvailableOms)-Sum(quantityAdvisedWms))
)
),'0') = '0','no','yes')

Tanks for your help.