Set analysis

[poojashribanger]

I have month_key and date field.

now i want to select yearstart i.e. April  month_key dynamically.

if it is 2018 then month_key should be 201804 .

(sum({<Month_key={"201704"} ,emp_cat_code={'R'},officer_cd = {'*'}-{'CD'}>} QTR_OPEN_CNT)+

   sum({<Month_key={"201710"} , emp_cat_code={'R'},officer_cd = {'*'}-{'CD''}>} QTR_CLOSE_CNT))/2)

Please help

[shraddha_g]

For Latest YearStart you can say

Date(YearStart(Max(Date),0,4),'YYYYMM') - this will give you Year Start of Month_Key for Selected Year

[poojashribanger]

i want to get monthkey

[poojashribanger]

you can see in my set analysis i have hard coded the value but i want it to get selected dynamically

[shraddha_g]

For this

sum({<Month_key={"201704"} ,emp_cat_code={'R'},officer_cd = {'*'}-{'CD'}>} QTR_OPEN_CNT)

You can use

sum({<Month_key={"$(=Date(YearStart(Max(Date),0,4),'YYYYMM'))"} ,emp_cat_code={'R'},officer_cd = {'*'}-{'CD'}>} QTR_OPEN_CNT)+

[poojashribanger]

if i select year as 2018 will it change dynamically to 201804?

[shraddha_g]

Yes..(If you have data for 201804)

[shraddha_g · Accepted solution]

Make this small change:

sum({<Month_key={"$(=Date(YearStart(Max(Date),0,4),'YYYYMM'))"} ,emp_cat_code={'R'},officer_cd = {'*'}-{'CD'},YearField=>} QTR_OPEN_CNT)