how to get this complicated requirement

kunkumnaveen — Fri, 14 Jul 2017 16:01:03 GMT

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

Re: how to get this complicated requirement

sunny_talwar — Fri, 14 Jul 2017 16:13:53 GMT

Try this

Sum({<Material={"=Count(Distinct {<Fiscal_Quarter, Fiscal_Month>} Id)>1"}>}Amount)

and this

Sum({<Material={"=Count(Distinct {<Fiscal_Quarter, Fiscal_Month>} Id)=1"}>}Amount)

topic how to get this complicated requirement in QlikView

how to get this complicated requirement

Re: how to get this complicated requirement