Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You missed < and >
Try this
Sum({<Year = {'2017'}>}Sales) - Sum({<Year = {'2016'}>}Sales)
Or may you need this
Sum(Sales) - Above(TOTAL Sum(Sales))
Hi Mohammad,
cay you share a sample app in order for us to help you?
Regards,
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
Try this
=Count({<DIM_ITEM.GroupMajorCode = {'<>071'}>} OrderNoLine)/1000 - Above(TOTAL Count({<DIM_ITEM.GroupMajorCode = {'<>071'}>} OrderNoLine)/1000)
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.
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)
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
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.