Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
m_monfort
Partner - Contributor III
Partner - Contributor III

Grouping/Aggregating based on variance %

Hi all

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


Sample.PNG

 

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)

Sample2.PNG

 

How do we do this with a QlikView load scipt?

 

I've attached the XLS data as an example.

 

 

Labels (1)
1 Solution

Accepted Solutions
zzyjordan
Creator II
Creator II

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.

 Capture.PNG

Hope this helps

 

ZZ

View solution in original post

3 Replies
zzyjordan
Creator II
Creator II

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.

 Capture.PNG

Hope this helps

 

ZZ

m_monfort
Partner - Contributor III
Partner - Contributor III
Author

Thanks very much! that works fine

zzyjordan
Creator II
Creator II

You are welcome 🙂
ZZ