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: 
sangeetha_changu
Contributor
Contributor

Display year wise static value across fields in Pivot table

We have year wise Contract Share% for 2018,2019. How to display the constant Contract Share % per year across the dimensions and KPIs in a pivot table 

Contract Share comes from an inline table in below format.. 

Share_DateShare_YearShare_MonthPlantCarrierContract Share (%)

 

and key has been created Share_Year&'_'&Share_Month&'_'&upper(Plant) &'_'& upper(Carrier) as %Key_ContractShare,

To Map the key in KPI table  i.e. Year([Ship Date])&'_'&Month([Ship Date])&'_'& upper(Plant) &'_'& upper(Carrier) as %Key_ContractShare,

 

Below is the screenshot which is showing wrong values for Contract Share %. Need help in correcting

contract.PNG

The actual Contract shares are below

  2019
CatalaoATRHOL50
CatalaoDICANALLI10
CatalaoDOLVITSCH10
CatalaoGEFCO5
CatalaoRODOMEU5
CatalaoSHOCK10
CatalaoTME10
18 Replies
sangeetha_changu
Contributor
Contributor
Author

 
sangeetha_changu
Contributor
Contributor
Author

 
Jesh19
Creator II
Creator II

Using your sample2.xls, I got the below solution.

 

CS.PNG

Max(Contract Share %) worked for me.

Thanks,

Jesh

sangeetha_changu
Contributor
Contributor
Author

sorry i already tried it but doesn't work. Please check the three attachments and sample QV file for reference and suggest the needful

Anil_Babu_Samineni

Try this?

Min(TOTAL <Carrier> [Contract Share (%)])

If this is not working, can you explain one row how the value calculated?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sangeetha_changu
Contributor
Contributor
Author

Hi Anil

The expression you suggested is working for few carriers --- Min(TOTAL <Carrier> [Contract Share (%)])

But for some of the carriers it is not working

To explain more about the rows, The Data from Sample file is the KPI data we get from Database and Sample2 is the inline data that we created for Contract share for each year based on plant and carrier. 

We are trying to link Contract Share data to KPI data through key i.e. %Key_ContractShare. Let me know if you need more details. We are looking for quick help. Thanks in advance

sangeetha_changu
Contributor
Contributor
Author

Hi Anil

This expression worked for us --- Min(TOTAL <Carrier> [Contract Share (%)])

But the 'Total' (Total per plant) in the Pivot table is showing the minimum value. As per the excel statis data for Contract Share the Total should always be 100% .

Could you please suggest on Total as well. Thanks in advance.

 

 

 

Anil_Babu_Samineni

Check this?

Sum(Aggr(Min(TOTAL <Carrier> [Contract Share (%)]), <Dim1>, <Dim2>, ..))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sangeetha_changu
Contributor
Contributor
Author

This worked.

Thanks a lot Anil