Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the table below where I am calculating a CW Count per Month my Expression is Count (Distinct [CW ID]).
The second expression calculated the difference between the Rows of the CW Count column
Expression is If(RowNo(TOTAL) = 1, 0, (Count (Distinct [CW ID])) - Above(Count (Distinct [CW ID])))
Question 1: How can I prevent the CW Count from calculating for Months that have not yet occurred. For example, I would want the table to end at March.
Question 2: How do I prevent the Monthly Difference column from calculating when the CW Count row is 0?
This is what I would like to see.
May be like this
If(Count(DISTINCT [CW ID]) > 0, If(RowNo(TOTAL) = 1, 0, (Count(DISTINCT [CW ID])) - Above(Count(DISTINCT [CW ID]))))
Thanks Sunny, That fixes my Question 2. Now, how can I prevent the CW Count from calculating for Months that have not yet occurred. For example, I would want the table to end at March.
Doesn't it resolve the 1st thing also? Everything April onward is 0 and will be hidden, no?
Thanks Sunny. I applied the If(Count(DISTINCT [CW ID]) > 0, to both expressions. and this is what I now get.
I have the suppress null check box turned on in the dimension, but it does not have an effect.
You need to check 'Suppress Zero Values' on the presentation tab.... also 1st expression doesn't need the if statement
It is checked for all columns
That is very interesting.... would you be able to share your qvw to see what is going on?
Actually, I just worked it out. The problem was that I was using the Month from the Calendar as my dimension. I switched it to the month that was specific for this data and that corrected the issue.
One follow up question, what would an expression look like to calculate the percentage change in CW Count from month to month?
Like this
Count(DISTINCT [CW ID])/Above(Count(DISTINCT [CW ID])) - 1