topic Re: Help me in getting Totals in Straight Table in App Development

Help me in getting Totals in Straight Table

d_prashanthredd — Tue, 21 Aug 2018 13:44:00 GMT

Hi,

I tried many ways to get the totals (by eliminating duplicates) but haven't succeeded.

Below is the overview of my base data and outputs: Just it is a sample and havemany other fields too.

Also attached the Qlik Sense App.

In my base data, ID (ID-3) and Product (PR-00003) repeated for different campaigns.

Now, my requirement is to calculate the total sockets (Quantity * Sockets) by Campaign and I achieved it. But the issue is with the grant Totals.

My grand total should be 38 but I'm getting it as 40 (considering duplicate values too).

I tried it many ways and at last using only function for calculation.

Note: At any instance (ID + Product) is unique.

Re: Help me in getting Totals in Straight Table

ogautier62 — Wed, 22 Aug 2018 07:05:09 GMT

Hi,

I see two ways to correct :

- first you change your model :

a) make two tables and not one to count once qty*sock

b) keep your model but when load feed only first row and not duplicate

- if you keep your model :

to eliminate duplicate usesomething llike aggr(max(qty)*max(socket),ID,Product)

and then use sum(this expression ) in your dimension campaign

regards

Re: Help me in getting Totals in Straight Table

d_prashanthredd — Wed, 22 Aug 2018 19:21:53 GMT

Hi Olivier,

Thanks but i can't change the data model or any changes in script editor.

Only i can do in front end that too in expressions.

Re: Help me in getting Totals in Straight Table

ogautier62 — Wed, 22 Aug 2018 21:34:57 GMT

So second solution above in front with aggr and max to take only one occurrence

Re: Help me in getting Totals in Straight Table

d_prashanthredd — Thu, 23 Aug 2018 07:20:10 GMT

Hi Oliver,

I tried that too, getting wrong values for Campaign-3.

Note: ID-3 and PR_00003 exists in both the campaigns Campaign-2 and Campaign-3. For Campaign-3 those values ignored but I don't know why. Value should be 10 instead 8.

Re: Help me in getting Totals in Straight Table

d_prashanthredd — Thu, 23 Aug 2018 07:47:09 GMT

Hi Tried differently, work around..

if(Dimensionality() = 0,

Sum(Aggr(max(Quantity) * max(Sockets), ID, Product)),

Sum(Aggr(max(Quantity) * max(Sockets), Campaign, ID, Product))

)

May be there is a better way to get it done but till now I haven't got any sollution.

Re: Help me in getting Totals in Straight Table

ogautier62 — Thu, 23 Aug 2018 08:38:29 GMT

that works with that :

sum(aggr(max(Quantity)*max(Sockets),campaign,id,Product))

I've got 10 and not 8

Re: Help me in getting Totals in Straight Table

d_prashanthredd — Thu, 23 Aug 2018 08:44:21 GMT

Row wise values are correct but total should be 38.

Above output is correct.

Re: Help me in getting Totals in Straight Table

ogautier62 — Thu, 23 Aug 2018 08:53:04 GMT

ah yes,

so with :

sum(aggr(max(Quantity)*max(Sockets),id,Product))

that works for both row/total

Re: Help me in getting Totals in Straight Table

ogautier62 — Thu, 23 Aug 2018 08:56:53 GMT

I'm not awake,

your solution with dimensionality() is good !!!

Re: Help me in getting Totals in Straight Table

d_prashanthredd — Thu, 23 Aug 2018 09:27:23 GMT

Thanks for your quick responses..