topic Re: How to write a expression in QlikView

How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 09:14:52 GMT

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]

))

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

Re: How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 09:18:14 GMT

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

Anil_Babu_Samineni — Thu, 11 Jan 2018 09:19:03 GMT

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

Re: How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 09:24:49 GMT

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

Re: How to write a expression

tresB — Thu, 11 Jan 2018 09:24:49 GMT

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]

))

Re: How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 09:36:35 GMT

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

Re: How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 09:37:49 GMT

the above expression giving me wrong total

Re: How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 09:40:30 GMT

Re: How to write a expression

tresB — Thu, 11 Jan 2018 09:42:41 GMT

Could you share your sample app?

Re: How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 10:19:08 GMT

Any more Suggestion on the above requirement ....

Re: How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 10:28:33 GMT

i managed to convert the zero value in to actual value by using below expression ,but the total i am getting is wrong

Expression:

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

How to sum rows

Re: How to write a expression

Anil_Babu_Samineni — Thu, 11 Jan 2018 10:54:08 GMT

What is the wrong here?

Re: How to write a expression

naveen_kunuru — Thu, 11 Jan 2018 11:02:49 GMT

Total,

It suppose to be sum of (2513340.18+29973.741=2543313.921)

but the total is 5033019.342

Re: How to write a expression

Anil_Babu_Samineni — Thu, 11 Jan 2018 11:10:12 GMT

Finally, The sub total will calculate over Total only for each measure, So for me it looks good, To get rid we need to use Aggr(). Off course, It's tough to say without having something. Will you attach sample?

Re: How to write a expression

Anil_Babu_Samineni — Mon, 15 Jan 2018 12:53:35 GMT

Please try to reply back ASAP or else it may wind out for long