get number of selected Months

[bharatkishore]

Hi All,

I have and Year field and Month field and Date field.

Now i have straight table and written an expression sum(consumption)*12.

Now  i need to divide the expression by number of selected months.

For ex: if i select year 2018 and select five months then in expression i should get sum(consumption)*12/5 and if i select 22 months expression should be sum(consumption)*22. The expression should be what ever months i select the expression should divide by number of months.

Please let me know if you need anything more.

Thanks,

Bharat

[pasi_lehtinen]

You can use GetSelectedCount() -function.

[bharatkishore]

HI
Thanks for your reply.
The problem is when i select 2 years then number of months is showing as 12 instead of 24 when i use get selected count.

[pasi_lehtinen]

If(GetSelectedCount(Year)=0,count(distinct Year),GetSelectedCount(Year))*If(GetSelectedCount(Month)=0,12,GetSelectedCount(12))

Edit:

Another solution would be to include YearMonth dimension into your Calendar. 

[MindaugasBacius]

I suggest simple method:
sum(consumption)*12 / Count(Distinct Year & Month)

[bharatkishore]

Thanks.
I have written the below expression ((sum(Consumption)*12)/GetSelectedCount(Year)*GetSelectedCount(Month))
but i am getting some wrong value.
If i give this expression i am getting this value i.e. 53,664,859
But when i give this expression (sum(Consumption)*12)/24) i am getting as 372,673 which is correct.
Can you please tell me where i am doing wrong..

[pasi_lehtinen]

Your divider should have parantheses (GetSelectedCount(Year)*GetSelectedCount(Month))

But actually a solution provided by MindaugasBacius is the one you should use.

[bharatkishore]

HI
Your expression is working fine when i select for one year and any number of months but when i select multiple years it is not working. Can you please help me where i am doing wrong..

[MindaugasBacius · Accepted solution]

There is the answer:
sum(consumption)*12 / Count(Distinct Year & Month)

[bharatkishore]

Thank you so much and thanks a lot..