Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a straight table where the data is displaying like this
SYSYTM ID JUN2016 JUL2016 AUG2016 SEP2016 OCT2016 NOV2016 COUNT_OF_IDS_EACHMONTH | POSSIBLEVALUES
A 1 10 0 20 10 30 20 90 5
B 2 0 0 0 0 0 0 0 0
C 3 5 0 5 10 20 0 40 4
My requiremnt is i want to calculate the POSSIBLE_VALUES field which calculates the count for every row
for example
in the below senario the POSSIBLE_VALUES is 5 because in july month there is no count and there is count in jun aug sep oct nov so it shows as 5
SYSYTM ID JUN2016 JUL2016 AUG2016 SEP2016 OCT2016 NOV2016 COUNT_OF_IDS_EACHMONTH | POSSIBLEVALUES
A 1 10 0 20 10 30 20 90 5
in the below example
there is no count for all months so ossible values is zero
SYSYTM ID JUN2016 JUL2016 AUG2016 SEP2016 OCT2016 NOV2016 COUNT_OF_IDS_EACHMONTH | POSSIBLEVALUES
B 2 0 0 0 0 0 0 0 0
in the below example
we have cunt for only 4 months so possible values is 4
SYSYTM ID JUN2016 JUL2016 AUG2016 SEP2016 OCT2016 NOV2016 COUNT_OF_IDS_EACHMONTH | POSSIBLEVALUES
C 3 5 0 5 10 20 0 140 4
Use Rolling 6 months as 1 and it will only calculate from Jun-Nov.
If( Date > monthstart(addmonths(today(),-5)) and Date <= today(),1) | as [Rolling 6], |
Use the above code in your Master Calendar & if you want rolling 5 monthts then -4 instead of -5....
And use [Rolling 6] in your expression front end.
count ({<POSSIBLEVALUES, [Rolling 6]={1}>} (YourDimension))
Or can you share your expression to correctly modify it?
Thanks.