CY-PY Sales in Pivot Table

[mhmmd_srf]

Hi All,

I have a requirement like below:

In the Act reduction field I need to show (2017 Sales - 2016 Sales) that is 0.207 - 0.018 = 0.189. And that too will show in the last row.

I am using simple Sum({Year = {'2017'}}Sales) - Sum({Year = {'2016'}}Sales) in expression. But it is not showing as expected.

Please help me to do that.

Thanks,

Sarif

[sunny_talwar]

You missed < and >

Try this

Sum({<Year = {'2017'}>}Sales) - Sum({<Year = {'2016'}>}Sales)

[sunny_talwar · Accepted solution]

Or may you need this

Sum(Sales) - Above(TOTAL Sum(Sales))

[santiago_respane]

Hi Mohammad,

cay you share a sample app in order for us to help you?

Regards,

[mhmmd_srf]

Hello Sunny,

My actual expression is below:

=(count({<DIM_ITEM.GroupMajorCode = {'<>071'},CstFiscalYear = {'2017'}>}OrderNoLine)/1000) - (count({<DIM_ITEM.GroupMajorCode = {'<>071'},CstFiscalYear = {'2016'}>}OrderNoLine)/1000)

Thanks,

Sarif

[sunny_talwar]

Try this

=Count({<DIM_ITEM.GroupMajorCode = {'<>071'}>} OrderNoLine)/1000 - Above(TOTAL Count({<DIM_ITEM.GroupMajorCode = {'<>071'}>} OrderNoLine)/1000)

[mhmmd_srf]

wow... I learned new thing.

I changed the calculation a bit. It is fine.

It is working now. I need one more help. I want to show this value only against 2017 row. I need to show blank in above two cell.

[sunny_talwar]

May be this

If(CstFiscalYear = 2017, Count({<DIM_ITEM.GroupMajorCode = {'<>071'}>} OrderNoLine)/1000 - Above(TOTAL Count({<DIM_ITEM.GroupMajorCode = {'<>071'}>} OrderNoLine)/1000))

or

If(CstFiscalYear = 2017, Count({<DIM_ITEM.GroupMajorCode = {'<>071'}, CstFiscalYear = {2017}>} OrderNoLine)/1000 - Count(TOTAL {<DIM_ITEM.GroupMajorCode = {'<>071'}, CstFiscalYear = {2016}>} OrderNoLine)/1000)

[mhmmd_srf]

Hello Sunny,

Sum(Sales) - Above(TOTAL Sum(Sales))

in the above expression, if we remove  TOTAL, then also it is showing same result.

any thoughts you have kept TOTAL in the expression.

Thanks,

Sarif

[sunny_talwar]

If you have a single dimension, then having TOTAL or not having a TOTAL will make no difference... the difference come when you have multiple dimensions... With TOTAL, the above will work regardless of the dimension. Without TOTAL, the above will look based on same set of first dimension.