topic Re: Row wise Sum after Multiplication. in QlikView

Row wise Sum after Multiplication.

sarfaraz_sheikh — Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I want to show the sum of rows after multiplication without aggregation . below is the example for clear level understanding.

I have below two columns [9LE Bulk] & [No of CR Pack On] which gets multiply and result will be your [Core Sold On premise] Column.

I am getting the expected result by using AGGR function but i do not want to use it as it is slowing down the performance while loading the chart at front end. I cannot shift it to back end also because both [9LE Bulk] & [No of CR Pack On] operating different dates columns on which user is making selection.

I want to do something in front end without aggregation and help would be much appreciated.

[9LE Bulk] =

Count({<NumDateKey={">=$(=vFromDate9LEBulk)<=$(=vToDate)"},FLAG={2,3},

[Secondary Diff to Primary Flag]={0,1}>} Distinct [9LE Bulk])

[No of CR Pack On] =

Sum({<[BDE Visit Frequency]-={'000','NO CALL','VIRTUAL'},[Secondary Diff to Primary Flag]={0,1},

[Valid from]={"$(=vValidFrom)"},[Valid to]={"2958465"}>}[No of CR Bulk on])

[Core Sold On Premise] = [9LE Bulk] * [No of CR Pack On] -------------Without Aggregation

[Core Sold On Premise] = Sum(Aggr([9LE Bulk] * [No of CR Pack On],Outlet,Product(IPL))) -------------WithAggregation

Without Aggregation:

With Aggregation:

QVW is also attached here for your reference based on sample data not on real time data.

Thanks

Sarfaraz

Re: Row wise Sum after Multiplication.

sunny_talwar — Tue, 17 Apr 2018 11:44:53 GMT

Are you multiplying them or adding them? multiplying 1 with 0 gives 0, but you have 1 as the output?

Re: Row wise Sum after Multiplication.

sarfaraz_sheikh — Tue, 17 Apr 2018 11:56:18 GMT

Hi Sunny ,

I am doing multiplication only .....you are viwing wrong column ....Please look at last column which is the actual result .

thanks

sarfaraz

Re: Row wise Sum after Multiplication.

sunny_talwar — Tue, 17 Apr 2018 12:02:09 GMT

I am still a little confused, but may be that is because you mentioned that the output was in Core Range on Premise... anyways... coming back the issue... the only thing which you need Sum(Aggr(...)) for is to get the totals right... otherwise everything else is working? Why don't you use Sum of Rows for total mode as you seem to be using a straight table here?

Re: Row wise Sum after Multiplication.

sarfaraz_sheikh — Tue, 17 Apr 2018 12:11:32 GMT

Thanks Sunny for your reply.

If you remove Product (IPL) from dimension then you will get 7 only even if you select Total Mode as sum of rows.

And I am using Qliksense. Can't we do something in such manner that will get 4 without Aggregation.

Thanks

Sarfaraz

Re: Row wise Sum after Multiplication.

sunny_talwar — Tue, 17 Apr 2018 12:26:44 GMT

I don't think it would be possible to do this without the aggregation

Re: Row wise Sum after Multiplication.

sarfaraz_sheikh — Tue, 17 Apr 2018 12:34:33 GMT

Data is huge ......and its taking time to much to load.

Anyway let me try from my end.

gwassenaar‌ ; hic‌ ; jagan‌ Please comment

Thanks

Re: Row wise Sum after Multiplication.

sunny_talwar — Tue, 17 Apr 2018 12:35:38 GMT

Sure

Re: Row wise Sum after Multiplication.

sarfaraz_sheikh — Wed, 18 Apr 2018 08:00:25 GMT

Can anybody please help me on this ?? Its on high priority and got stuck here.

hic‌ ; jagan‌ please comment

Thanks

Sarfaraz

Re: Row wise Sum after Multiplication.

Anil_Babu_Samineni — Wed, 18 Apr 2018 08:57:35 GMT

Is there any specific reason to avoid Aggregation over measure?

Re: Row wise Sum after Multiplication.

sarfaraz_sheikh — Wed, 18 Apr 2018 09:09:10 GMT

For performance Optimization .....Its taking long time for processing while reloading chart as i have 10+ crores of records.

Thankk