Hello All, My requirement is to differentiate multi ids and single ids materials when respective fiscal year or month or quarter selected
Fiscal_Year Fiscal_Quarter Fiscal_Month Material Id Amount
2017 Q1 Apr C001 D01 10
2017 Q1 Apr A001 D01 10
2017 Q1 Apr A001 D02 50
2017 Q1 Apr B001 D01 20
2017 Q2 Aug A001 H05 90
2017 Q3 Oct B001 H05 60
2017 Q4 Jan L001 N06 50
and when i select only Fiscal year = 2017
Table-1
Dimension:Material,Id
Expression For Table-1 is used: Sum({<Material={"=Count(Distinct Id)>1"}>}Amount)
Table-2
Dimension :Material,Id
Expression For Table-2 is used: Sum({<Material={"=Count(Distinct Id)=1"}>}Amount)
I am getting below table which is perfect
Multi ids table Single ids table
--------------- -----------------
Material Id Amount Material Id Amount
A001 D01 10 C001 D01 10
A001 D02 50 L001 N06 50
A001 H05 90
B001 D01 20
B001 H05 60
and when i select Fiscal year 2017 and either month Apr or Quarter Q1, i am get below tables, in which material B001 has moved to single ids table ,
Actually which was in multi ids table when only Fiscal year 2017 was selected
Multi ids table Single ids table
--------------- ---------------
Material Id Amount Material Id Amount
A001 D01 10 C001 D01 10
A001 D02 50 B001 D01 20
but what i need is,i want Material B001 in multi ids table even though Apr or Q1 has selected
because when only 2017 is selected this material is having more then one ids.
Required below table when Apr or Q1 has selected
Multi ids table Single ids table
-------------- --------------
Material Id Spend Material Id Spend
A001 D01 10 C001 D01 10
A001 D02 50
B001 D01 20
can any one suggest me what changes do i need to do in my expression to achieve above requirement
thanks
