Help me in getting Totals in Straight Table

[d_prashanthredd]

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.

[ogautier62]

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

[d_prashanthredd]

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.

[ogautier62]

Ok

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

[d_prashanthredd]

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.

[d_prashanthredd]

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.

[ogautier62]

that works with that :

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

I've got 10 and not 8

[d_prashanthredd]

Row wise values are correct but total should be 38.

Above output is correct.

[ogautier62]

ah yes,

so with :

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

that works for both row/total

[ogautier62]

I'm not awake,

your solution with dimensionality() is good !!!