Sum Distinct Value

john_Talampas — Sun, 08 Sep 2019 23:03:10 GMT

Hello,

I have a request from a user to look at the "DATE" field, below, and to grab the total number of discharges that occurred 30 days prior.

I'm able to build, into my data model, the discharges that occurred 30 days prior and group them into days:

Date - 30	Discharges
12/9/2018	253
12/22/2018	273
12/30/2018	253
1/1/2019	273
	1,052

I am using the "DATE - 30" to tie the prior discharges to the current "DATE".

CalendarYearMonth	DATE	DATE - 30	Plan	Total Acct	Discharges -30 days	Unmatched Volume
2019-01	2019-01-08 00:00:00.000	2018-12-09 00:00:00.000	MINNESOTA GEN MCP M	2	253	0.8%
2019-01	2019-01-08 00:00:00.000	2018-12-09 00:00:00.000	MONTANA M	2	253	0.8%
2019-01	2019-01-08 00:00:00.000	2018-12-09 00:00:00.000	SOUTH DAKOTA M	22	253	8.7%
2019-01	2019-01-08 00:00:00.000	2018-12-09 00:00:00.000	NEBRASKA TC	1	253	0.4%
2019-01	2019-01-08 00:00:00.000	2018-12-09 00:00:00.000	NEBRASKA WC	0	253	0.0%

2019-01	2019-01-21 00:00:00.000	2018-12-22 00:00:00.000	SOUTH DAKOTA M	4	273	1.5%

2019-01	2019-01-29 00:00:00.000	2018-12-30 00:00:00.000	MONTANA M	1	253	0.4%
2019-01	2019-01-29 00:00:00.000	2018-12-30 00:00:00.000	SOUTH DAKOTA M	5	253	2.0%
2019-01	2019-01-29 00:00:00.000	2018-12-30 00:00:00.000	WYOMING M	4	253	1.6%

2019-01	2019-01-31 00:00:00.000	2019-01-01 00:00:00.000	DENVER HM CHO	1	273	0.4%
2019-01	2019-01-31 00:00:00.000	2019-01-01 00:00:00.000	WYOMING M	1	273	0.4%
			This is what I have	43	526	8.2%


			This is what I want	43	1052	4.1%

While this is fine I can not seem to calculate correctly the total number of discharges.

So for "CalendarYearMonth" of 2019-01 I have 43 "Total Acct". I'm trying to produce a calculation that will show the total distinct discharges of 1052; not the 526 that I currently have.

Any insight on how to correctly calculate the total discharges will be greatly appreciated.

John T

Re: Sum Distinct Value

StarinieriG — Mon, 09 Sep 2019 08:48:39 GMT

Hi,

try in this way:

If(Dimensionality()=0,Sum(Aggr(Max([Discharges -30 days]),[DATE - 30])),your expr.)

Re: Sum Distinct Value

john_Talampas — Tue, 10 Sep 2019 05:59:16 GMT

That did the trick. Thank you

topic Re: Sum Distinct Value in App Development

Sum Distinct Value

Re: Sum Distinct Value

Re: Sum Distinct Value