topic Re: Issue with bucket creation in QlikView

Issue with bucket creation

paulwalker — Fri, 15 Nov 2024 23:38:59 GMT

Hi Community,

I'm facing issue with bucket creation, can you please help me.

Data:

Country	Salary	Base Sal	Revenue
IND	500	2000	1200
IND	1200	300	200
IND	250	300	700

I have different calculations, like below....

Country	Bucket	Calculation
IND	BASE	Salary/Revenue
BENEFIT	(Salary-Base Sal) / Revenue
BONUS	(Revenue-Base Salary) / Revenue

if i select India, have to show below..

Thanks in Advance!

Re: Issue with bucket creation

Taoufiq_Zarra — Wed, 11 Nov 2020 09:09:23 GMT

@paulwalker May be :

in dimension

=Valuelist('BASE','BENEFIT','BONUS')

and in measure

=Pick(Match(Valuelist('BASE','BENEFIT','BONUS'),'BASE','BENEFIT','BONUS'), sum(Salary)/sum(Revenue), (sum(Salary)-sum([Base Sal]))/sum(Revenue), (sum(Revenue)-sum([Base Sal]))/sum(Revenue) )

Re: Issue with bucket creation

paulwalker — Wed, 11 Nov 2020 09:16:38 GMT

I have huge data, is there any possibility script level ??

Re: Issue with bucket creation

Taoufiq_Zarra — Wed, 11 Nov 2020 09:34:50 GMT

@paulwalker If I understood correctly you can do also in script:

Data: LOAD * INLINE [ Country, Salary, Base Sal, Revenue IND, 500, 2000, 1200 IND, 1200, 300, 200 IND, 250, 300, 700 ]; Tmp: load Country,sum(Salary)/sum(Revenue) as BASE,(sum(Salary)-sum([Base Sal]))/sum(Revenue) as BENEFIT,(sum(Revenue)-sum([Base Sal]))/sum(Revenue) as BONUS resident Data group by Country; Final: CrossTable(Measure, Data) LOAD * resident Tmp; drop table Tmp;

and then in UI

Dimension is Measure Field

and Expression sum(Data) for example