Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Compute proportion between two tables

Hi,

I am working on Qlik Sense and looking for a way to compute the proportion of equal data between two tables.

For example, let's assume we have two sales tables, Sales A and Sales B, which are the number of sold items A and B day by day during 5 years.

Do you know a way to compute the proportion of days where we have sold the same amount of items A and B ?

6 Replies
mdmukramali
Valued Contributor II

Re: Compute proportion between two tables

Dear Eleyeh,

Can you attached sample data file .

Thanks,

Mukram

Not applicable

Re: Compute proportion between two tables

Hi Mukram,

Here's a data sample :

Sales ASales B

1

1
42
53
94

15

15
1111
34
1218
04
11
87

So in this example each table has 11 lines and 4 lines have equal values in both columns. So the desired output should be 4/11

vinieme12
Esteemed Contributor II

Re: Compute proportion between two tables

Try as below

TableA:

LOAD [Sales A] ,  Rowno() as RowKEY

FROM

[https://community.qlik.com/thread/274005]

(html, codepage is 1252, embedded labels, table is @1);

TableB:

LOAD

     [Sales B] , Rowno() as RowKEY

FROM

[https://community.qlik.com/thread/274005]

(html, codepage is 1252, embedded labels, table is @1);

now create a straight table with dimensions

RowKEY, Sales A , Sales B


add below expression

sum(if([Sales A]=[Sales B],1))


or in a textbox


=sum(if([Sales A]=[Sales B],1)) & ' / ' & count(DISTINCT RowKEY)      /// string 4 / 11

or

=sum(if([Sales A]=[Sales B],1))   /  count(DISTINCT RowKEY)      // Percentage 4 / 11



can you provide something that closely matches the dataset you are working with?


mdmukramali
Valued Contributor II

Re: Compute proportion between two tables

Dear Eleyeh,

As you mentioned in your first comment you have two tables 

How those Two Tables are Connected with each other  ? (Based on Date ).

IF based on Date field then we can create a flag in the backend to check on each date SAELS A and SALES B if

both are Equal then flag value 1 else 0 and we can use that flag in the front end.

if you can share your sample data with the same structure it will good for us to help you in better way.

Thanks,

Mukram.

Not applicable

Re: Compute proportion between two tables

Thanks Vineeth and Mukram for you answers !

I am sorry but I unfortunately can't give a sample of the data I work with. However I can give more details about my dataset. So Sales A and Sales B are two tables that are loaded separately from the database. Each tables has two columns : one with the quantity of sales per day and another column with the date the sales are made. So the tables are connected by the date. The rest of it really similar to the example I've given.

Thanks again for you answers, I will try them tomorrow !

vinieme12
Esteemed Contributor II

Re: Compute proportion between two tables

We don't need a real sample of your data!

But you can create dummy data that represents your dataset.