Calculate expression only for one dimension value

[#1]

Hi together,

I need to calculate some KPIs (say, KPI1, KPI2, KPI3)  for different regions and years with expressions in a pivot table.

My dimensions are Region and Year (2013 - 2016). The KPI2 can only be calculated for the year 2015 (for other years there is no relating data existing, so the expression value is either 0 or null), other KPIs can be calculated for all years.

This means, only for the year 2015 the column KPI2 should be showed (I pivoted the dimensions so in the pivot table the first column is the regions and years are in the first row).

I've ticked 'Suppress When Value is Null' for dimensions and 'Suppress Zero-Values' for expressions (in the Presentation tab) but the column KPI2 is still showed for all years with the value 0 and null. I also tried Conditional and set condition as 'KPI2<>0 and KPI2<>null', but it does not work either.

Is there any other way to solve this problem??

Many thanks!

BR

Jenny

[Anil_Babu_Samineni]

Can you please share Screen shot

[sunny_talwar]

Would you be able to share a sample qvw to see the issue and also provide what exactly would you want your final result to look like.

Best,

Sunny

[krishnacbe]

Hi,

You should write a condition in Expression tab -> check the Conditional check box and write the condition to show/hide.

Hope this helps.

[#5]

Thanks you guys for the reply.

@Krishnapriya I tired your solution, but it didn't work... the column KPI2 has just disappeared...

Attached is the screen shot of the sample data, the KPI2 is 0 for all years except 2015, but even I use the condition KPI2<>0 it is still showed for all years...

Would be great if anybody can find out why...

Thanks in advance...

BR

Jenny

[sasikanth · Accepted solution]

HI,

Table object work row level,

Region	Year	KPI1	KPI2	KPI3
South	2016	100	0	120
North	2016	200	0	500
South	2015	100	200	300

IN 2016 there is no data for KPI2

but still values are available for KPI1 and KPI3 so it will not be suppressed by default.and it is valid.

if you force to suppress the rows where KPI2 is NULL or ZERO then you miss other KPIs related data.

Please let me know if it is not the solution you are looking for..

Thanks,

[#7]

I see...I think you are right.

Many thanks for the explanation !