Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
JAN. 24, Q&A with Qlik: Making the Move to Qlik Cloud Reporting: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ElsKnockaert
Contributor III
Contributor III

Pivot Table Exception for one Row Value

Good morning

I have a pivot table that works as intended.

In this table we work on Prod_Id as a first level, then Empl_Id and at last Cust_Chain.

The goal is to see per product, in which chain of customer each employee , has checked how many stores have this product present (#Presence) and how many stores they still have to check for this product to reach their target (To Do).

This pivot table is working well and to the satisfaction of the client.

 

ElsKnockaert_0-1657700688176.png

But! And here it comes, the client wishes for an exception for product SL0051 (not any other product, those have to remain the same), that only user RSS07 (not any other user, they also have to remain the same) gets an additional Cust_Chain, Carrefour Express.

By changing my variable, I'm able to include this new chain without any issue. But the difficulty lies that this chain is automatically added for all products and all other employees

ElsKnockaert_1-1657701002042.png

The expression used for example in column Maximum Distribution, for the original table is:

sum(

    aggr(

               count({< Prod_Id = P(D1_ProdFilter_Prod_Id), $(vDistributionLimitation) >} Prod_Id)

              *count({$<$(=vScopeCust), $(vDistributionLimitation) >} Cust_Id)

    , Prod_Descr,Cust_Territory_Id,Cust_Chain)

)

 The variable vDistributionLimiation is set as follows:

ElsKnockaert_2-1657701199847.png

To try my adjustment I added Carrefour Express to the field Cust_Chain_Distr_Obj_Id, but naturally this results in Carrefour Express showing up for all users and products.

I've tried with altering my variable, using if statements in my expression but I can't seem to figure this out.

Always grateful for any input!

Labels (5)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

you don't need {} braces in if(), that is incorrect

=if( Emp_ID = 'RS007' and Prod_ID= 'Sl00'

, then

, else

)

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

4 Replies
vinieme12
Champion III
Champion III

use an IF() block Measure

=if(Emp_ID='RSS07',

<ExpressionINCLUDINGCarrefourExpressChain>

,

<ExpressionEXCLUDINGCarrefourExpressChain>

)

 

This has to be done for all measures in the pivot table

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
ElsKnockaert
Contributor III
Contributor III
Author

Unfortunately this did not help, I've tried this before but now I altered it for all measures as you suggested.

It shows all products in the database, not only those P(D1_ProdFilter_Prod_Id) and everything is a blank

ElsKnockaert_0-1657703439706.png

Expression for first measure was altered with variable _Exception1 where Carrefour Express was included :

ElsKnockaert_1-1657703568036.png

 

 

vinieme12
Champion III
Champion III

you don't need {} braces in if(), that is incorrect

=if( Emp_ID = 'RS007' and Prod_ID= 'Sl00'

, then

, else

)

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
ElsKnockaert
Contributor III
Contributor III
Author

Thank you so much! 

I've added the If statement into a variable to make it somewhat more dynamic.

ElsKnockaert_0-1658216370798.png

ElsKnockaert_1-1658216388674.png