13 Replies Latest reply on Sep 11, 2019 12:17 PM by Deepak Rai

FIXED LOD Calculations

Hello,

I have data for an LMS. Year of completion is 2017. Email address of employee. Module names and

If Module Passed = 1 (Completion Status)

Module Failed = 0

Module Passed X times (more than 1) = 2,3,4,5.... (Sum of Completion Status)

For the 4th Module.. the user completed it 3 times...

I need a count of all Users that completed Module X times (more than just once)

I put the formula for Xtimes count as:

(If SUM([Completionstatus])>=2

then

1

ELSE

0

END

)

Now.. I want to remove all the fields.. and only have the count for user completed Module X times to show up... I am guessing I'd need a FIXED cal?

The result should be such

2017    1 user. (in the screenshot)

2018    XXX

2019   XXX

{ FIXED [Email], [Contentname]:[Xtimes Count]}???

Thank you very much for helping in advance.

• 1. Re: FIXED LOD Calculations

I currently have this:

When I remove the field content name.. it gives me error..

I need to sum the measure values while I remove the fields email and content name..

The result should be like

2016  x

2017  7

2018  x

• 2. Re: FIXED LOD Calculations

You have some data?or workbook

• 3. Re: FIXED LOD Calculations

I cannot share

• 4. Re: FIXED LOD Calculations

Can you drag those items in the rows to detail.

• 5. Re: FIXED LOD Calculations

is your first logic correct which is giving you this?

• 6. Re: FIXED LOD Calculations

You can do Window_SUM restarting every year or can do fixed LOD. If you have some fake data that will work too.

• 7. Re: FIXED LOD Calculations

This doesnt work

{ FIXED YEAR([Completiondate]),[Email], [Contentname]:[Xtimes Count]}

What am I doing wrong?

• 8. Re: FIXED LOD Calculations

{ FIXED YEAR([Completiondate]):SUM({ FIXED YEAR([Completiondate]),[Email], [Contentname]:SUM(IF{ FIXED YEAR([Completiondate]),[Email], [Contentname]: COUNT([Xtimes Count]})>=2 Then 1 else 0 end)})}

1 of 1 people found this helpful
• 9. Re: FIXED LOD Calculations

Error: Expected Closing Paranthesis or comma while passing argument list for COUNT.

Deepak I really appreciate this, I know its difficult without seeing the workbook, thank you for your patience and efforts.

• 10. Re: FIXED LOD Calculations

That is only typo error. please create some thing with superstore.

• 11. Re: FIXED LOD Calculations

did it work?

• 12. Re: FIXED LOD Calculations

no.

But thanks for all the help. I did it using Excel.