topic Display year wise static value across fields in Pivot table in QlikView

Display year wise static value across fields in Pivot table

sangeetha_changu — Thu, 05 Dec 2019 11:52:38 GMT

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_Date

Share_Year

Share_Month

Plant

Carrier

Contract 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

The actual Contract shares are below

		2019
Catalao	ATRHOL	50
Catalao	DICANALLI	10
Catalao	DOLVITSCH	10
Catalao	GEFCO	5
Catalao	RODOMEU	5
Catalao	SHOCK	10
Catalao	TME	10

Re: Display year wise static value across fields in Pivot table

siddheshmane — Thu, 05 Dec 2019 11:23:48 GMT

Hi,

what is your expression for Contract Share%

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Thu, 05 Dec 2019 11:42:03 GMT

sum(Contract Share%) which isn't working.

Please suggest an expression to display the static value for Contract share

Re: Display year wise static value across fields in Pivot table

Anil_Babu_Samineni — Thu, 05 Dec 2019 11:45:28 GMT

You need to manipulate with Num() format?

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Thu, 05 Dec 2019 12:37:31 GMT

Re: Display year wise static value across fields in Pivot table

Jesh19 — Fri, 06 Dec 2019 04:00:26 GMT

From your question, I understood that values should always remain static.

You can acheive it by showing it as a dimension or an expression.

For dimension: =Num([2019]/100,'#,##0%')

For expression: Sum([2019])/100 and Under Number tab, change it to Fixed to and Enable 'Show in Percentage'

Thanks,

Jesh

Re: Display year wise static value across fields in Pivot table

Anil_Babu_Samineni — Fri, 06 Dec 2019 06:04:22 GMT

@sangeetha_changu if you simply paste the image, I strongly believe that won't help for others instead what you have handy in the expression that may helps for sure

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Fri, 06 Dec 2019 06:44:00 GMT

i was just using some trial expressions like

sum({<Year={$(vMaxYear)}>}Contract Share%)

Max({<Year={$(vMaxYear)}>}Contract Share%)

i even directly used [Contract Share %] field in expression

which is not giving me the desired results

Re: Display year wise static value across fields in Pivot table

Anil_Babu_Samineni — Fri, 06 Dec 2019 06:47:58 GMT

Do you have some sample data set which concur the same?

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Fri, 06 Dec 2019 07:18:33 GMT

Please find the attached sample qvw

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Fri, 06 Dec 2019 09:20:22 GMT

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Fri, 06 Dec 2019 07:20:15 GMT

Re: Display year wise static value across fields in Pivot table

Jesh19 — Fri, 06 Dec 2019 09:01:29 GMT

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

Max(Contract Share %) worked for me.

Thanks,

Jesh

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Fri, 06 Dec 2019 09:06:58 GMT

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

Re: Display year wise static value across fields in Pivot table

Anil_Babu_Samineni — Fri, 06 Dec 2019 09:41:17 GMT

Try this?

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

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

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Fri, 06 Dec 2019 10:52:06 GMT

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

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Sun, 08 Dec 2019 16:47:05 GMT

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.

Re: Display year wise static value across fields in Pivot table

Anil_Babu_Samineni — Tue, 10 Dec 2019 07:23:49 GMT

Check this?

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

Re: Display year wise static value across fields in Pivot table

sangeetha_changu — Fri, 13 Dec 2019 12:19:50 GMT

This worked.

Thanks a lot Anil