Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
)
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
Any suggestions please
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