Qliksense Pivot Table, Row(?) percentage instead of counts

[samuel_lin]

Hello Qliker!!

I have a pivot table that looks like:

The expression for counts right  now is very simple, it's just count(distinct EmailAddress)

I want to replace, but unsuccessful so far, counts to % of the row. For example, for row #1 '0X~3X', each column should be about 10%, the first one under high is 10,307/97,373 and so forth.

Any idea how I can achieve this?

Thanks!

Samuel

[samuel_lin]

just to be clear, I would like to get this:

Thank you so much for the help!

[sunny_talwar · Accepted solution]

Try this may be:

Count(DISTINCT EmailAddress)/Count(DISTINCT TOTAL <reseller_Segment> EmailAddress)

[sunny_talwar]

Or you can try this:

Count(DISTINCT EmailAddress)/Aggr(NODISTINCT Sum(Aggr(Count(DISTINCT EmailAddress), reseller_Segment, [propensity to buy])), reseller_Segment)

[samuel_lin]

You're so smart, thank you Sunny!!

[sunny_talwar]

Thanks Samuel!!! I am glad I was able to help.

Best,

Sunny

[samuel_lin]

Hey Sunny,

May I please get your help with a similar thing too? Again, I have a pivot table:

but the percentage is not calculating the way I want it.

First of all, Digital Segment 3, is created as a dimension in the UI

when I want to apply your solution from last time, expression doesn't pick up Digital Segment 3 as a dimension

given this, is there a way to calculate something like:

Thank you so much for your help!!

[sunny_talwar]

I think the best approach here would be to create the calculated dimension in the script and then you won't run into any issues here:

Count(DISTINCT EmailAddress)/Count(DISTINCT TOTAL <Dimension2> EmailAddress)

But I doubt you will be able to do this with a calculated dimension.

[samuel_lin]

You're right, Sunny.

Here's the calculated dimension that I have:

Aggr(

  If([first order date]  > (today()-90), Dual('New', 1),

If([last order date] > (today()-270) and [TotalOrderCount] = 1, Dual('Recent OneTime Buyer', 2),

  If([last order date] > (today()-270) and [TotalOrderCount] > 1, Dual('Active', 3),

  If([first order date] <= (today()-90) and [last order date]  <= (today()-270) and [last order date] > (today()-365), Dual('At Risk', 4),

  If([last order date]  <= (today()-365), Dual('Lapsed', 5),

  If([FirstOrderDate] = '', Dual('Non Buyer', 6)

  ))))))

, EmailAddress)

In the backend script load, I was trying to duplicate the dimension:

Load if([first order date]  > (today()-90),[EmailAddress]) as EmailAddress,

  'New' as DigitalSegment_2

Resident [Full_List_Order_History];

Concatenate

Load if([first order date]  <= (today()-90) and [last order date] > (today()-270) and [TotalOrderCount] = 1 ,[EmailAddress]) as EmailAddress,

  'Recent OneTime Buyer' as DigitalSegment_2

Resident [Full_List_Order_History];

Concatenate

Load if([first order date]  <= (today()-90) and [last order date] > (today()-270) and [TotalOrderCount] > 1 ,[EmailAddress]) as EmailAddress,

  'Active' as DigitalSegment_2

Resident [Full_List_Order_History];

Concatenate

Load if([first order date] <= (today()-90) and [last order date]  <= (today()-270) and [last order date] > (today()-365),[EmailAddress]) as EmailAddress,

  'At Risk' as DigitalSegment_2

Resident [Full_List_Order_History];

Concatenate

Load if([last order date]  <= (today()-365),[EmailAddress]) as EmailAddress,

  'Lapsed' as DigitalSegment_2

Resident [Full_List_Order_History];

Load if([FirstOrderDate] = '', [EmailAddress]) as EmailAddress,

  'Non Buyer' as DigitalSegment_2

Resident [Full_List_audience-1];

The problem with the backend load is that [TotalOrderCount] is from another table [Full_List_audience-1], and from reading some qlik discussion posts, I am still not clear how to have Resident from two tables...

Appreciate  your help!

[sunny_talwar]

There are few ways to go about this

1) Join the two tables and do your manipulation on the joined table

Table1:

LOAD....

Resident...;

Left Join (Table1)

LOAD....

Resident....;

Table2:

LOAD ....,

          NEW_FIELD....

Resident Table1;

2) Use MappingTable/ApplyMap (preferable because you only need one field from another table)

MappingTable:

Mapping

LOAD ....

Resident....;

Table:

LOAD ....

           ApplyMap('MappingTable', FieldName) as NEWFIELD

Resident.....;

3) Use Lookup function....