topic Re: Expression Challenge in QlikView

Expression Challenge

priya945 — Sat, 16 Nov 2024 00:57:42 GMT

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

Re: Expression Challenge

Vegar — Fri, 27 Mar 2020 12:32:45 GMT

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)
)

Re: Expression Challenge

priya945 — Tue, 31 Mar 2020 06:57:18 GMT

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

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

Re: Expression Challenge

priya945 — Mon, 30 Mar 2020 16:15:27 GMT

Any suggestions please

Re: Expression Challenge

Brett_Bleess — Tue, 21 Apr 2020 19:23:04 GMT

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