Expression Challenge

[priya945]

Hi All,

I have below data in excel pivot, calculating All and 3 other Regions(North,South East).

In qlikview the expression for Region = All is bellow, below expression returns '-' in Pivot table. Where Region 'All' is not part of the original data. But calculated 

if(Region = 'All', ((Avg({Region={'North'}>}Time) *count({<Region={'North'}>} distinct [Serial Number])) +
(Avg({<Region={'South'}>}Time) * count({<Region={'South'}>} distinct [Serial Number]))+
(Avg({<Region={'East'}>} Time) * count({<Region={'East'}>} distinct [Serial Number])))/count(distinct [Serial Number]) ,

Avg(Time)
)

Thanks in advance

[Vegar]

Could a Serial number be associated to more than one Region?

Maybe you can write like this instead?

if( Region = 'All',
    Avg({<Region={'North','South','East'}>}Time),
    Avg(Time)
   )

[priya945]

Hi,

Each Serial number  is associated to only one Region and Region 'All' 

Also Region ='All' is not part of the original data so deriving  as below

Regon:
LOAD * Inline [
SubRegion, Region
East,East
West, West
North,North
East,All
West, All
North,All
];

and below expression is showing  hash(-) in place of a value for Region 'All' in pivot table

if(Region = 'All',   ((Avg({Region={'North'}>}Time) *count({<Region={'North'}>} distinct [Serial Number])) +
(Avg({<Region={'South'}>}Time) * count({<Region={'South'}>} distinct [Serial Number]))+
(Avg({<Region={'East'}>} Time) * count({<Region={'East'}>} distinct [Serial Number])))/count(distinct [Serial Number]) ,

Avg(Time)
)

But below expression shows correct value in a Textbox when 'All' is selected in Region field

((Avg({Region={'North'}>}Time) *count({<Region={'North'}>} distinct [Serial Number])) +
(Avg({<Region={'South'}>}Time) * count({<Region={'South'}>} distinct [Serial Number]))+
(Avg({<Region={'East'}>} Time) * count({<Region={'East'}>} distinct [Serial Number])))/count(distinct [Serial Number])

Unable to figure it out the issue . Please suggest 

Thanks in advance

[priya945]

Any suggestions please

[Brett_Bleess]

What would likely help most is if you can attach a sample QVW file, little difficult to sort out otherwise, which is why you are not getting further responses.

The best I can do otherwise would be:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

You may be able to use the latter in lieu of the ALL Region potentially, but I am not sure, I am not a great developer, but hopefully these may give you some further ideas if you cannot attach a sample application.

Regards,
Brett