RunningCounts in Webi not bringing distinct counts for month

server administration Web Intelligence 6.x Servers
1

Hi All,

I am trying to achieve cumulative distinct counts for each month.
I have created a measure in IDT to get the total count of users who logged in each month.

SELECT
count(distinct case when ( MYC_PT_USER_ACCSS.MYC_UA_TYPE_C )=1 then ( MYC_PT_USER_ACCSS.MYPT_ID ) else Null end) as ‘Total User count’

FROM
MYC_PT_USER_ACCSS
WHERE
(
MYC_PT_USER_ACCSS.UA_TIME <= ‘07/30/2014 23:59:59’
AND
MYC_PT_USER_ACCSS.USER_ID Is Null
)

But if I try to add this count to a webi report and create a variable called monthYear for each month based on MYC_PT_USER_ACCSS.UA_TIME, the counts are changing. How do I get a distinct cumulative count by each month upto July.

I tried to use the measure I created in IDT in webi it did not give me correct counts when I pull in Access Time in the query to create months the counts change.
So i created a variable in webi
1 for monthyear which is (=month(MYC_PT_USER_ACCSS.UA_TIME)+" "+year(MYC_PT_USER_ACCSS.UA_TIME)
2nd one for distinct counts as =Count([MYC Patient System ID];Distinct).

But when I bring counts by each month, then the counts are wrong. Please see attachment for what I am getting, reason being is I need to bring in Unique counts by each month without the counts bleeding in from next month.
test.PNG

bobjfan2015 (BOB member since 2015-05-29)

2

Any updates on the above question? I want to have a count on distinct logins based on max of date. How do I achieve the cumulative count for each month.

bobjfan2015 (BOB member since 2015-05-29)