topic Re: User defined standards: Group TOTAL expression by pivot dimension in QlikView

User defined standards: Group TOTAL expression by pivot dimension

femkescholten — Fri, 19 Oct 2018 15:32:29 GMT

Hello,

I'm having some difficulties building an expression. What we need is to compare object values to a standard/control object that can be selected from a list (in an alternate state called Standards).

In the first pciture you see it works fine for 1 Charcteristic (K), in the second picture you see what happens when all Characteristict (K, N, Na) are selected. The standard is averaged over all Characteristics but I would like to have this standard grouped by characteristic too. Does anyone know how to accomplish that?

Any help would be great!!!

The expressions:

Avg =Avg(_Value)

Std =Avg(TOTAL {<Object={$(vStandards)}>} _Value)

% Of Std =(Avg(_Value)/Avg(TOTAL {<Object={$(vStandards)}>} _Value))

And variable:

vStandards =Concat(DISTINCT {Standards} chr(39) & Object & chr(39), ',')

Re: User defined standards: Group TOTAL expression by pivot dimension

dplr-rn — Fri, 19 Oct 2018 17:29:38 GMT

Use TOTAL <Characteristic>

Re: User defined standards: Group TOTAL expression by pivot dimension

femkescholten — Mon, 22 Oct 2018 10:28:44 GMT

Hello Dilip,

The answer you give doesn't work, it gives me the total average, not per Characteristic and Selectected objects, nevertheless thanks for looking into the question.

I need something like:

=(Avg(_Value)/Avg(TOTAL {<Characteristic={ ... here goes the characteristic of the pivot dimension },Object={$(vStandards)}>} _Value))

Regards,

Femke

Re: User defined standards: Group TOTAL expression by pivot dimension

dplr-rn — Mon, 22 Oct 2018 17:52:29 GMT

check the attached qvw to check if the output is correct. if not let me know in a table format the desired output

Re: User defined standards: Group TOTAL expression by pivot dimension

femkescholten — Tue, 23 Oct 2018 06:30:12 GMT

Hello Dilip,

Indeed, that's perfect!! Thanks so much for the help!!

Kind regards,
Femke

Re: User defined standards: Group TOTAL expression by pivot dimension

dplr-rn — Tue, 23 Oct 2018 16:39:07 GMT

no problem