topic Re: CY-PY Sales in Pivot Table in QlikView

CY-PY Sales in Pivot Table

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: CY-PY Sales in Pivot Table

sunny_talwar — Mon, 07 Aug 2017 12:36:32 GMT

You missed < and >

Try this

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

Re: CY-PY Sales in Pivot Table

sunny_talwar — Mon, 07 Aug 2017 12:37:17 GMT

Or may you need this

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

Re: CY-PY Sales in Pivot Table

santiago_respane — Mon, 07 Aug 2017 12:39:25 GMT

Hi Mohammad,

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

Regards,

Re: CY-PY Sales in Pivot Table

mhmmd_srf — Mon, 07 Aug 2017 12:41:45 GMT

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

Re: CY-PY Sales in Pivot Table

sunny_talwar — Mon, 07 Aug 2017 12:44:33 GMT

Try this

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

Re: CY-PY Sales in Pivot Table

mhmmd_srf — Mon, 07 Aug 2017 12:53:57 GMT

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.

Re: CY-PY Sales in Pivot Table

sunny_talwar — Mon, 07 Aug 2017 12:56:23 GMT

May be this

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

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

Re: CY-PY Sales in Pivot Table

mhmmd_srf — Thu, 07 Dec 2017 13:33:37 GMT

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

Re: CY-PY Sales in Pivot Table

sunny_talwar — Thu, 07 Dec 2017 13:36:25 GMT

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.

Re: CY-PY Sales in Pivot Table

mhmmd_srf — Thu, 07 Dec 2017 13:40:33 GMT

Got it ..

thanks a lot..