Grouping/Aggregating based on variance %

[m_monfort]

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.

[zzyjordan · Accepted solution]

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

ZZ

[m_monfort]

Thanks very much! that works fine

[zzyjordan]

You are welcome 🙂
ZZ