Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I have a requirement to display actuals and budget for all months in a selected year, in which i have always one value selected in Year and Month fields. For eg , if March is selected, Jan , Feb & Mar should display actuals whereas Apr to Dec should display budget numbers. Can anyone pls help on this?
Attaching dummy data for the same
Regards,
Anilet Nirmal
Hi,
Attached an alternate approach to the same.
Expression:
=if(only({1}DATE(DATE_SKEY,'MM'))<=vMonth,sum({<DATE_SKEY,RevenueType={'Actuals'}>}Data)
,sum({<DATE_SKEY,RevenueType={'Budget'}>}Data))
Background color Expression:
=if(only({1}DATE(DATE_SKEY,'MM'))<=vMonth,LightCyan()
,LightMagenta())
Variable:
vMonth =DATE(DATE_SKEY,'MM')
LOAD SCRIPT
CrossTable(RevenueType, Data)
LOAD DATE_SKEY,
Actuals,
Budget
FROM
(ooxml, embedded labels, table is Sheet1);
Hi
You need to add a Bar chart and having the follwoing
Dimension
Month(DATE_SKEY)
Measures
sum(Actuals)
sum(Budget)
Chart
Hi Nirmal
Dim : Month
Expressions :
Actuals Sum({<Month={'<=$(=Max(Month))'}>} Actuals)
Budget Sum({<Month={'>$(=Max(Month))'}>} Budget)
Hi,
Attached an alternate approach to the same.
Expression:
=if(only({1}DATE(DATE_SKEY,'MM'))<=vMonth,sum({<DATE_SKEY,RevenueType={'Actuals'}>}Data)
,sum({<DATE_SKEY,RevenueType={'Budget'}>}Data))
Background color Expression:
=if(only({1}DATE(DATE_SKEY,'MM'))<=vMonth,LightCyan()
,LightMagenta())
Variable:
vMonth =DATE(DATE_SKEY,'MM')
LOAD SCRIPT
CrossTable(RevenueType, Data)
LOAD DATE_SKEY,
Actuals,
Budget
FROM
(ooxml, embedded labels, table is Sheet1);
Hi Vineeth,
I applied the below expression but the if condition is not satisfied and only budget values are displayed for all months without actuals. Can you please check my expression and i can not use two expressions for actuals and budget as I am using a stacked bar to calculate other measures. kindly help on this.
My expression is
if(only({1}DATE(DATE_SKEY,'MM'))<=vMonth,
sum({<DATE_SKEY,SEGMENT={'RBG'},INDICATOR_TYPE='ACTUALS'},tablename='AVGBS'},
YEAR=$(=max(YEAR))},MONTH=>}NII_REPORT_CURR)/1000
,sum({<DATE_SKEY,SEGMENT={'RBG'},INDICATOR_TYPE={'BUDGET'},tablename={'AVGBS'}
,YEAR={$(=max(YEAR))},MONTH=>}NII_REPORT_CURR)/1000)
Variable:vMonth =DATE(DATE_SKEY,'MM')
Regards,
Anilet Nirmal
You expression is missing Parenthesis, highlighted in bold
if(only({1}DATE(DATE_SKEY,'MM'))<=vMonth,
sum({<DATE_SKEY,SEGMENT={'RBG'},INDICATOR_TYPE= { 'ACTUALS'},tablename={'AVGBS'},
YEAR={$(=max(YEAR))},MONTH=>}NII_REPORT_CURR)/1000
,sum({<DATE_SKEY,SEGMENT={'RBG'},INDICATOR_TYPE={'BUDGET'},tablename={'AVGBS'}
,YEAR={$(=max(YEAR))},MONTH=>}NII_REPORT_CURR)/1000)
im so sorry , i missed the braces while editing but my expression is same as urs and im still having the same issue that only budget values are displayed for all months. I think the if condition is not satisfied so its going to else part .
my dimension is MONTH and I have ALWAYS ONE VALUE SELECTED VALUE in YEAR & MONTH fields.
Kindly help
if(only({1} DATE(DATE_SKEY,'MM')) <=vMonth,
sum({<
DATE_SKEY
,SEGMENT={'RBG'},INDICATOR_TYPE={'ACTUALS'}
,tablename={'AVGBS'}
,YEAR={$(=max(YEAR))}
,MONTH=
>}
NII_REPORT_CURR)/1000
,
sum({<
DATE_SKEY
,SEGMENT={'RBG'},INDICATOR_TYPE={'BUDGET'}
,tablename={'AVGBS'}
,YEAR={$(=max(YEAR))}
,MONTH=
>}
NII_REPORT_CURR)/1000
)
Hi Nirmal,
Sorry Didn't notice this earlier,
DATE(DATE_SKEY,'MM')) <=vMonth << Here I was converting the date_skey to Numeric month
I notice you already have a MONTH Field, for which you are ignoring selections. Is this from a separate table ? If not why not evaluate MONTH <= vMonth?
,YEAR={$(=max(YEAR))}
,MONTH=
>}
also can you create a clone of the chart , make it a straight table and add the below expressions(Below) to validate if the Month is being evaluated correctly.
= if(only({1} DATE(DATE_SKEY,'MM'))<=vMonth,1,0)
= only({1}DATE(DATE_SKEY,'MM'))
also check if vMonth =DATE(DATE_SKEY,'MM') is being evaluated correctly
My Screenshot for reference
May be something like this
sum({1<DATE_SKEY={"<=$(=only(DATE_SKEY))"}>}Actuals) + sum({1<DATE_SKEY={">$(=only(DATE_SKEY))"}>}Budget)