topic Re: Displaying Sum Total Per Category Ignoring Month Dimension in QlikView

Displaying Sum Total Per Category Ignoring Month Dimension

makximenkoa — Fri, 21 Sep 2018 21:38:33 GMT

Hello,

I have a table something like below:

Month	Denial Code	Denied Amount
Jan	1	$45
Jan	2	$65
Jan	3	$75
Feb	1	$45
Feb	2	$65
Feb	3	$75
Mar	1	$45
Mar	2	$65
Mar	3	$75

What I am attempting to do is add another column that lists the total sum of all Denied Amounts per Denial Code. In theory, this table would look something like below:

Month	Denial Code	Denied Amount	Total
Jan	1	$45	$135
Jan	2	$65	$195
Jan	3	$75	$225
Feb	1	$45	$135
Feb	2	$65	$195
Feb	3	$75	$225
Mar	1	$45	$135
Mar	2	$65	$195
Mar	3	$75	$225

The closest I've gotten has been

sum(TOTAL <Month> [Denial Amount])

, although it instead lists the total amount of each month on every line (in the case of the example above, $185 would be stamped on each line since the Denied Amounts totaled the same number every month).

I tried various various versions of that statement wrapped in AGGR to no avail.

Any help would be most appreciated!

Thanks

Re: Displaying Sum Total Per Category Ignoring Month Dimension

lironbaram — Sat, 22 Sep 2018 04:00:52 GMT

it seems that sum(total <[Denial Code> [Denial Amount] ) should work

have a look at the attach example

Re: Displaying Sum Total Per Category Ignoring Month Dimension

michaelnoyen — Wed, 10 Oct 2018 10:22:32 GMT

Hi together,

I really like Lirons solution:

sum(total <[Denial Code]> [Denial Amount] )

Is it possible just to ignore the time dimension? We would like to reuse the expression in several charts/tables, so we have a long list of dimensions, that should not be ignored:

sum(total <[Sub product], Product, [Product category], [Product level], [Product area], Client, [Local Party], [Customer group]> Amount )

I would like to have a shorter expression. Something in this direction:

sum(total <not(Date)> Amount )

Is this possible with Qlik syntax?

Thanks

Michael

Month	Denial Code	Denied Amount
Jan	1	$45
Jan	2	$65
Jan	3	$75
Feb	1	$45
Feb	2	$65
Feb	3	$75
Mar	1	$45
Mar	2	$65
Mar	3	$75

Month	Denial Code	Denied Amount
Jan	1	$45
Jan	2	$65
Jan	3	$75
Feb	1	$45
Feb	2	$65
Feb	3	$75
Mar	1	$45
Mar	2	$65
Mar	3	$75

Month	Denial Code	Denied Amount
Jan	1	$45
Jan	2	$65
Jan	3	$75
Feb	1	$45
Feb	2	$65
Feb	3	$75
Mar	1	$45
Mar	2	$65
Mar	3	$75