Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

naveen_kunuru
New Contributor

How to write a expression

Hello All,

                  I need help in writing a expression ,the below table as three dimensions

                                                                                           1)Material Code,2)Vendor code,3)Plant code

        and Four expressions    

                                             1)Residual:    sum({<[FISCAL YEAR]=>}Resi)

                                             2) VolumeFY2017(16-17):  Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)                                                                3)    Column(1)*Column(2)

                                            4)Savings Potential :

sum(aggr(

Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)*sum({<[FISCAL YEAR]=>}Resi])

,[MATERIAL CODE],[VENDOR CODE],[PLANT CODE]

))

Capture.PNG

So the problem is when i use column(1)*column(2) iam getting 29973.741(203.903*147) which is correct but

when i am trying to use along with aggr function i am getting Zero ,

so kindly any one suggestion what am i missing in my expression 4th, or is there any other way to write the above expression

Thanks

14 Replies
naveen_kunuru
New Contributor

Re: How to write a expression

        1)Residual:        sum({<[FISCAL YEAR]=>}Resi)


                                             2) VolumeFY2017(16-17):  Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)        

                                                      

                                            3)    Column(1)*Column(2)


                                            4)Savings Potential :

sum(aggr(

Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)*sum({<[FISCAL YEAR]=>}Resi)

,[MATERIAL CODE],[VENDOR CODE],[PLANT CODE]

Re: How to write a expression

But, Column(1) and Column(2) won't written Aggr() then why you are trying with Aggr() in third column?

naveen_kunuru
New Contributor

Re: How to write a expression

i need to show only column 4 ,i just displayed rest of the column for clear understanding purpose

MVP
MVP

Re: How to write a expression

Are expression 3 and 4 supposed to return same results? if so, why are you trying with aggr() when you get that using column() function?  Meanwhile, you can try like:

sum(aggr( NODISTINCT

Sum({<[FISCAL YEAR]={'$(=max([FISCAL YEAR])-1)'}>}VOLUME)*sum({<[FISCAL YEAR]=>}Resi)

,[MATERIAL CODE],[VENDOR CODE],[PLANT CODE]

))

naveen_kunuru
New Contributor

Re: How to write a expression

the total which suppose to get from expression 4   is correct (2513340.18+29973.741=2543313.921),

the total i am getting from column(1) * column(2) is wrong ,thats why i am trying to use aggr function to get correct total

naveen_kunuru
New Contributor

Re: How to write a expression

the above expression giving me wrong total

naveen_kunuru
New Contributor

Re: How to write a expression

Capture.PNG

MVP
MVP

Re: How to write a expression

Could you share your sample app?

naveen_kunuru
New Contributor

Re: How to write a expression

Any more Suggestion on the above requirement ....

Community Browser