Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Contributor III

how to write aggr expression

Hello All,

              A weird thing is happening to me i don't know whether it is my expression fault or my data modeling

Capture.PNG

i am trying to display total spend by grouping plant name and material name ,what i mean is ,

the 3rd and 4 th row, same material ,same plant but different suppliers so i need to group both and total spend will be same which is working perfect

the 1st row has only one supplier so total spend should be 80.27 but i am getting nil  as total spend which is wrong

Dimensions: 1)calculated dimension===>if(wildmatch(New_Column1,'Non-Zpart'),New_Column)  Label  as Material

                      2)Plant Name

                      3)Plant Code

                       4)Supplier

                       5)Supplier ID

         

Measure :1) Sum({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}Volume)

                 2)Sum({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}SPEND)

                3)Colume(2)/Colume(1)

                4)aggr(nodistinct Sum({<SCOPE={'Yes'},MATL_TYPE={'HALB','ROH'},[FISCAL YEAR]={$(=max([FISCAL YEAR])-1)}>}SPEND),New_Column,[PLANT NAME] )

all the expression are working accept this 4 expression which is working partially ,what could be the reason any suggestion plz




Thanks

4 Replies

Re: how to write aggr expression

How about this when we talk about partial

Sum(Aggr(...Sum(...) ...))

kunkumnaveen
Contributor III

Re: how to write aggr expression

i just find this

before adding Interm Parent id and price_mat total spend is working fine

Capture1.PNG

after adding  Total spend is not working

Capture2.PNG

what could be the reaso

kkkumar82
Valued Contributor III

Re: how to write aggr expression

How about writing your sum like

Sum(total <Material,PlantName> your expression)

sasiparupudi1
Honored Contributor III

Re: how to write aggr expression

You May not have the calculated dimensions in the Aggr function.

I suggest you created the New_column in the script as a fixed dimension and try again

hth

Sasi

Community Browser