topic Re: Average expression based on two distinct text fields in QlikView

Average expression based on two distinct text fields

ZoeM — Sat, 16 Nov 2024 04:15:24 GMT

Hello Community.

Trying to create an expression that calculates the average based on distinct fields on 2 parameters or more.

From the illustration at the below, would it be possible to change the expression as shown?

=sum(Aggr( DISTINCT Program, Stage='Final'),[#of Days Post VPP])

Program is a text field

Stage is a text field and I need to pull in only Final information

[#of Days Post VPP] is a numerical field and I need to sum this up after the two previous parameters have been met

Thanks for all the help rendered!

Re: Average expression based on two distinct text fields

crusader_ — Thu, 07 Mar 2019 04:24:48 GMT

Hi,

It's not quite clear what is expected result, anyway....

Aggr() function always return a virtual table with distinct values (unless explicitly specified nodistinct).

Don't really understand what's the purpose of "Program" field in your case...

Can't you just use sum( {$<Stage={'Final'}>} [#of Days Post VPP]) ?

Hope this helps.

//Andrei

Re: Average expression based on two distinct text fields

ZoeM — Thu, 07 Mar 2019 12:38:12 GMT

Thanks for the reply.

I need program to be distinct because there are multiple instances of the same criteria, i.e. program>stage>final...because the data set contains programs that have multiple line items representing different attributes hence the need to create definitions on what to pull.

Hope that helps?