Count in staright table for last six months

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Count in staright table for last six months

MK9885 — Tue, 22 Nov 2016 20:34:11 GMT

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.