how to use set analysis in if condition.

[infosense_devel]

Hello,

i have requirement to show graph line up to today, for that i have used below formula.

if(num(Common_Month)<=num(month(today())),

((VDSOETargetYearly * (100 * Rangesum(above(total (Sum({<REC_TYPE={InvoiceDateRec},[Common_Day (#)]=,

Common_Month=>} (CTLA_EXTENDED_AMOUNT * XRATE_CONST * RATE))

+ Sum({<Common_Month=,[Common_Day (#)]=,REC_TYPE = {'OfflineData'}>}ACCTD_AMOUNT*XRATE_CONST*RATE))

,0,rowno(total))))/Rangesum(above(total sum({<PATH_SELECT =,

Common_Month =,[Common_Day (#)]=,REC_TYPE = {'BudgetRec'}>}DAILY_BUDGET),0,rowno(total))))/100),

null())

by this formula i can achieve my goal but this formula works only when user not select month, as soon as user select month is that line shows data for that month only.

Please take a look at attached screenshot "graph_req.png".

[Gysbert_Wassenaar]

As a quick fix you can try this:

if(num(only({<Common_Month=>}Common_Month))<=num(month(today())),

((VDSOETargetYearly * (100 * Rangesum(above(total (Sum({<REC_TYPE={InvoiceDateRec},[Common_Day (#)]=,

Common_Month=>} (CTLA_EXTENDED_AMOUNT * XRATE_CONST * RATE))

+ Sum({<Common_Month=,[Common_Day (#)]=,REC_TYPE = {'OfflineData'}>}ACCTD_AMOUNT*XRATE_CONST*RATE)) 

,0,rowno(total))))/Rangesum(above(total sum({<PATH_SELECT =,

Common_Month =,[Common_Day (#)]=,REC_TYPE = {'BudgetRec'}>}DAILY_BUDGET),0,rowno(total))))/100),

null()

)

But it's better to get rid of the if statement and replace Common_Month= with Common_Month={'<=$(=num(month(today()))'} everywhere.

[infosense_devel]

Thanks for your reply @Gysbert Wassenaar,

Your reply helped me, but still i am not able achieve my goal.

with you help i have modified my formula as below,

if(num(only({<Common_Month=>}Common_Month))<=num(month(today())),

((VDSOETargetYearly * (100 * Rangesum(above(total (Sum({<REC_TYPE={InvoiceDateRec},Common_Month={'>=$(=only(Common_Month))<=$(=only(Common_Month)+0)'}>} (CTLA_EXTENDED_AMOUNT * XRATE_CONST * RATE))

                        + Sum({<Common_Month={'>=$(=only(Common_Month))<=$(=only(Common_Month)+0)'},REC_TYPE = {'OfflineData'}>}ACCTD_AMOUNT*XRATE_CONST*RATE)) 

                ,0,rowno(total))))/Rangesum(above(total sum({<PATH_SELECT =,

                               Common_Month={'>=$(=only(Common_Month))<=$(=only(Common_Month)+0)'},

                               REC_TYPE = {'BudgetRec'}>}DAILY_BUDGET*XRATE_CONST*RATE)

                ,0,rowno(total))

         ))/100)

         ,null())

Now, in above formula as you can see i have used Today() so it is not restricting line at selected month (Common_Month), if i am replacing num(month(today())) with num(common_month) than i am getting data only for selected month.

Please take a look at attached image.

[Gysbert_Wassenaar · Accepted solution]

Try if(num(only({<Common_Month=>}Common_Month))<=$(=num(Common_Month))

And make sure to use the exact case sensitive field name common_month, Common_month and Common_Month are three different field names.

[infosense_devel]

Thanks a lot gwassenaar‌