Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Pivot Table

Hi All,

Can anyone help me with this?

My data is in the attached spreadsheet. I need to create a pivot table that more or less looks like the chart below.

The column 'Annual Turns' represents the buckets (<0, 0-0.25)

The column 'Amount' represents the values in the pivot table.

Untitled.png

So for distributor A, we should get :

<0: $540

0-0.25: $4322

0.25-0.5: $56962

0.5-0.75: $0

0.75-1: $0

>1: $3947344

1 Solution

Accepted Solutions
devarasu07
Master II
Master II

Hi,

you can try to create logical amount age bucket like below,

Fact:

load *,

if( [Annual Turn] <=0,Dual('<0',1),

if( [Annual Turn] >0 and [Annual Turn] <=0.25,Dual('<0-0.25',2),

if( [Annual Turn] >0.25 and [Annual Turn] <=0.5,Dual('<0.25-0.5',3),

if( [Annual Turn] >0.5 and [Annual Turn] <=0.75,Dual('<0.5-0.75',4),

if( [Annual Turn] >0.75 and [Annual Turn] <=0.1,Dual('<0.75-0.1',5),Dual('>1',6) ))))) as AnnualTrun_AgeGroup;

LOAD Distributor,

     [Part Number],

     Amount,

     [Annual Turn]

FROM

(ooxml, embedded labels, table is Sheet1);

Note: based on your data o/p should return below,

Capture.JPG

View solution in original post

4 Replies
devarasu07
Master II
Master II

Hi,

you can try to create logical amount age bucket like below,

Fact:

load *,

if( [Annual Turn] <=0,Dual('<0',1),

if( [Annual Turn] >0 and [Annual Turn] <=0.25,Dual('<0-0.25',2),

if( [Annual Turn] >0.25 and [Annual Turn] <=0.5,Dual('<0.25-0.5',3),

if( [Annual Turn] >0.5 and [Annual Turn] <=0.75,Dual('<0.5-0.75',4),

if( [Annual Turn] >0.75 and [Annual Turn] <=0.1,Dual('<0.75-0.1',5),Dual('>1',6) ))))) as AnnualTrun_AgeGroup;

LOAD Distributor,

     [Part Number],

     Amount,

     [Annual Turn]

FROM

(ooxml, embedded labels, table is Sheet1);

Note: based on your data o/p should return below,

Capture.JPG

Anonymous
Not applicable
Author

Thanks for your reply! For my dashboard I have to create the age buckets as calculated dimension and it works great!

So under dimensions I have : 1) Distributor

                                                2) Calculated dimension -Annual Turn

Expression: Amount

Do you know how I can add a total column per distributor? Thanks!

Untitled.png

untitled2.png

devarasu07
Master II
Master II

Hi,

Yes we can do that. by enabling chart property presentation tab-->Partial sum on the Distributor dimension then it should return the desired output. thanks

Capture.JPG

Regards,

Deva

Anonymous
Not applicable
Author

Thank you