topic Grouping/Aggregating based on variance % in QlikView

Grouping/Aggregating based on variance %

m_monfort — Sat, 16 Nov 2024 04:57:14 GMT

Hi all

I have a table with 3 columns like below (CRM, Brand as measures and Var as%)

I want to group this into a table which counts the unique CRM ID's and groups them by Brand based on their variance

Below is a sample of that (it does not have the exact figures in there, these are just a guide)

How do we do this with a QlikView load scipt?

I've attached the XLS data as an example.

Re: Grouping/Aggregating based on variance %

zzyjordan — Mon, 17 Dec 2018 04:01:40 GMT

Hi, there

Try this

[Sheet1]:
LOAD [CRM ID],
[Brand],
[Bgt Var %],
if([Bgt Var %] <-0.2, '<-20%',if([Bgt Var %]>=-0.2 and [Bgt Var %]< -0.1,'-20% to -10%','-10% to 0')) as "Bgt Var% Range"
FROM [lib://AttachedFiles/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

And then in pivot table, drag the new column and brand as dimension, count(distinct [CRM ID]) as measure.

Hope this helps

Re: Grouping/Aggregating based on variance %

m_monfort — Tue, 18 Dec 2018 05:40:46 GMT

Thanks very much! that works fine

Re: Grouping/Aggregating based on variance %

zzyjordan — Tue, 18 Dec 2018 06:23:44 GMT

You are welcome 🙂
ZZ