Selecting few values and creating calculated dimension for pivot table

[sanp96]

Hey everyone,

I am working on a Pivot table which has to be built based on an already created table chart. 

The table looks something like this:

Store Number	Store name	Account number	Account type	Amount
1	ABC	1234	L	73
1	ABC	2536	M	932
1	ABC	6666	L	46
2	PQR	8726	O	15
2	PQR	1234	L	126
3	XYZ	2536	M	87
3	XYZ	9287	P	36
3	XYZ	6666	L	98

I do not want all the account types in the pivot. I am trying to get only account types 'L','M', and 'O' into the Pivot and calculate the % of total by diving the amount with the sum of all amount for that particular account number, and displaying only the topmost value for that account number, which would look something like this:

Store number	store name	account number	account type	amount	% of total
2	PQR	1234	L	126	63.31%
3	XYZ	6666	L	98	68.05%
1	ABC	2356	M	932	91.46%
2	PQR	8726	O	15	100%

This would have been a very simple  SQL query had I been working on that, but I am new to Qlik and this seems more complicated.

I read a couple of responses in other forums which suggest creating a variable but I am not sure how would that help me because I am kind of like grouping it by the account number. 

Any help or suggestions is highly appreciated.

Thank you!

[Andrei_Cusnir]

Hello,

I was reading through the information that you have provided, but I was confused with the second table image that you have included. If my understanding is correct, you have the dataset (that is described in the initial table) and you want to create a new Pivot Table that will include only the values of data where Account type is 'L', 'M' and 'O'. From the second image of the table that you have uploaded, it seems that some of the values are missing, so I wasn't sure if this is the expected data or you just uploaded part of it for demonstration. For example I can see that you want to display all the Account types 'L' but the second table has only 2 of those records visible, while the initial dataset contains 4 records.

I believe that in your use case scenario, it will be helpful to use a combination of Filter object or in case you want to make this with a SQL statement, you can use the WHERE clause in the Data load editor. Here are the workarounds:

Workaround 1:

1. Create the Pivot table
2. Add the dimensions / measures as needed
3. Create a Filter pane object and as dimension add "Account type"
4. If you select the "L", "M", "O" from the Filter pane your Pivot table will be adjusted to present the values that you want
5. If you want to make the Pivot table and Filter pane not change based on other Chart selections, then continue with the next steps
6. Go to Master items > Alternate states 
7. Create a new state and call it "Pivot Table for L M O"
8. Go to Pivot table and in Filter pane and change the state under "Appearance > Alternate states" from "<Select a value>" to "Pivot Table for L M O"
9. You will notice that the selected values in Filter pane will not affect any other visualizations, and any selections in other visualizations, will not affect your Filter pane and your Pivot table.

Workaround 2:

1. You can use WHERE clause in Data load editor to create a new table and limit the loaded data to include only records where Account type is either L, M or O.
2. The script will look like this:

NewData:
LOAD 
    [...]
Resident OriginalData
WHERE [Account type] = 'L' or [Account type] = 'M' or [Account type] = 'O';

I hope that this information was helpful. In case it was not, or in case I have misunderstood the use case scenario, please elaborate further on how the Pivot table should look like. If you can recreate the table's look in Excel and share a screenshot, we can help you recreated it in Qlik Sense. Because at the moment, the second table doesn't look like a Pivot table and I have the feeling that some records are missing from the preview which makes it hard to understand how exactly you would like the table to look and which values it should present.

[sanp96]

Hi Andrei_Cusnir,

Thank you for a very detailed reply. Probably the way I framed the question wasn't very clear. The reason there are only a few rows in the second table is that I wanted to get only those rows that have the highest % of Total for a particular account number for a particular store. 

To get the % of Total I used, 

Num(sum(amount)/sum(Total<accountnumber>amount), '#.00%')

Although this requirement has changed now, Instead of getting only the topmost % of total per account per store, I am looking to get only rows >60% and <-60% 

Any suggestions on how this can be done?

[Andrei_Cusnir]

Hello,

Thank you clarifying further your use case scenario. I was looking into the details that you have shared and I have a few more clarification questions. Please share with us the following details:

1. You have a dataset where 1 Store Number has multiple Account Numbers.
2. Each Account Number is associated with only 1 Account Type. e.g. Account Number 1234 is only associated with Account Type 'L' etc.
3. Different Account Numbers can be associated with the same Account Type. e.g. 1234 is associated with Account Type 'L' and 6666 is associated with Account Type 'L' etc.
4. You have different Amount for different combinations of Store Number and Account Number. e.g. 1 & 1234 = 73, 1 & 6666 = 46 etc.

Now with this information you want to:

1. Calculate the % of total by summing the "Amount" and dividing by the TOTAL sum where the Account Number is the same.
2. You want to display only the values that are higher than -60% and lower than 60%
3. You want to exclude the accounts with Account Type = 'P'
4. You want to present this information on a Pivot Table

Since the requirements have changed, clarifying this information, will help the Qlik community understand better what are the exact requirements of the use case scenario. Additionally, if you can create in EXCEL how this table should look like, it will definitely help understand even better what exactly is needed. You don't have to apply the complicated expressions in the EXCEL, just write down the correct value that is supposed to be displayed, after the table is constructed and share with us a screenshot of the data.

[sanp96]

Andrei_Cusnir, 

The summary of the details you shared is correct. Just a small correction- I am only interested in the % values that are greater than 60% and lower than -60%

I have attached an Excel Pivot table snippet. I am looking to created something like that. 

Unfortunately, I cannot share the actual data and I did not include the negative values in the sample i.e negative account balances (amount) but that would be pretty much like the others.

So far what I have tried in QlikSense is as follows:

1. Created a Pivot table and added the first dimension as AccountNumber by using:

Aggr(if(accounttype = 'L' or accounttype = 'M'  or accounttype = 'O', AccountNumber ), AccountNumber)

2. Similar expressions for other dimensions like storeNumber and accounttype

3. Added 'Amount' as the first measure and second measure as '% of Total' as:

Num(sum(amount)/sum(Total<accountnumber>amount), '#.00%')

The values that I have obtained with this are accurate. The only issue I am facing now is getting all values that are greater than 60% and less than -60% and sorting those values. I know the sorting part is tricky, but hopefully, I'm able to get the first part at least.

Hope this helps and has clarified things. Let me know if any other information is needed.

Thanks!

[Andrei_Cusnir · Accepted solution]

Hello,

Thank you very much for sharing with me this information. I was able to get the view that you have with a different workaround. Although my workaround might not be the best, it might give you few ideas on how this can be implemented differently and it might also help you build on top of it for better results. Here are the steps that I have followed and the corresponding results:

1. First, I have created a simple Pivot Table and added 'Account Type', 'Account Number' and 'Store Number' as dimensions. Then I have added 'Sum(Amount)' and 'Num(sum(Amount)/sum(Total<[Account number]>Amount), '#.00%')' for '% of Total' as measures. This gave me the following visualization:

As you can see, all the values are visible (including for Account Type P) and also the values that are between -60% and 60%, which means that non of the criteria is met yet.

2. To eliminate the Account Type P, I have modified only the first dimension 'Account Type' from the visualization. I have replaced the '=[Account Type]' with '=If([Account type] = 'P', Null(), [Account type])', which will check the account type and will return Null() value if the account type equals to P, otherwise it will return the account type itself. Then I have unchecked the option 'Include null values' and since in for Account Type there was "-" (Null) value instead of 'P' the entire group disappeared. The new Pivot Table looks like that:

  

As you can see the Account Type P is not presented on the Pivot Table.

3. The next requirement is to eliminate all the values that are between -60% and 60%. To do that, I have replaced the second measure '% of Total' from 'Num(sum(Amount)/sum(Total<[Account number]>Amount), '#.00%')' to:

'=if(

    sum(Amount)/sum(Total<[Account number]>Amount) > 0.6

    or

    sum(Amount)/sum(Total<[Account number]>Amount) < -0.6,

    Num(sum(Amount)/sum(Total<[Account number]>Amount), '#.00%'),

    Null()

)'.

This will first check if the equation is either grater than 0.6 or less than -0.6 and then will return the result of expression that will give you the ##%, otherwise it will give you "-" (Null). Then you can go to the "Add-ons > Data handling" and uncheck the option 'Include zero values'. 

Up until this point, you will notice that the rows that are within the range -60% and 60% have Null(), but are NOT disappeared: 

The reason for that, is because the entire row needs to be "-" (Null), so that it could disappear. In this case, a similar logic was used for the other measure as well and it was changed from '=Sum(Amount)' to:

'if(

    sum(Amount)/sum(Total<[Account number]>Amount) > 0.6

    or

    sum(Amount)/sum(Total<[Account number]>Amount) < -0.6,

    Sum(Amount),

    Null()

)'.

This equation will check again if the expression matches the criteria and in that case it will return the Sum(Amount), otherwise, it will return the Null value.

Since both measures, will return the values only if the same expression is met, this means that both measures will have Null values in the same exact spot. This will result in a Pivot Table such as:

The only issue with this, is the fact that the row for Account Type L and O has disappeared, because the value in that spot is 24.91% and 1.09% respectively, which is outside the requirement. This is easily visible in the following representation:

As you can see, the value for the row where Account Type = L is 24.91%, the value for Account Type = O is 1.09% and thus it will be excluded from the view. This can be fixed if you include the method Dimensionality() in the logic of the If statement, which returns 1 if you are checking the value on the first level, 2 if you are checking the value on the second level etc. Then you can modify the logic to return the Null() or the % value only if the dimensionality is NOT equal to 1.

I hope that this information was helpful and that it will allow you to build additional logic on top of that, which will allow you to achieve your use case scenario.

[sanp96]

Andrei_Cusnir, 

Thank you for a very detailed reply. This seems to be working. Thank you for your help. Much appreciated!