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.
bobjfan2015 (BOB member since 2015-05-29)