Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I hope you all are doing good,
I want to create bar chat in which i want to show year wise sales.
eg:- I want to display Current year sales in one bar and last year till date sales in other bar which i have achieve by using following code.
Dimension- Fiscal Year
Expression-
if(FiscalYear=vMaxYear,SUM({<FiscalYear={'$(vMaxYear)'},Date={'<=$(vMaxDate)'}>}Actual_Amount)/Factor,
if(FiscalYear=vPriorYear,SUM({<FiscalYear={'$(vPriorYear)'},Date={'<=$(vPriorYearDate)'}>}Actual_Amount)/Factor))
Now i want show percentage based on these expression i.e if current year sales is 100 and last year sales is 90 than the calculation will be (100-90/100)*100.
I am getting challenge because i have to use last year amount till date sales with current year till date.
Thanks in advance
Hi Vinesh,
Can you help me out how to create YTD & PYTD flag in script level & that should be fiscal year flag. how can i take both flags in field.
firstly, you can simplify your expression by the use of Flags. You won't need IF
Add YTD and PYTD flags to your table your expressions should then be easier to work with
example
dimension = Period
Expression = SUM()
SUM({<Period = {'YTD','PYTD'}>}Actual_Amount)/Factor
% calculation
(SUM({<Period = {'YTD'}>}Actual_Amount) - SUM({<Period = {'PYTD'}>}Actual_Amount)) / SUM({<Period = {'PYTD'}>}Actual_Amount)
Period Presets: Compare Periods on the fly
I think you will need to use Above() function or The As-Of Table to get this done.
May be something like this:
1 - (Above(Sum({<FiscalYear = {'$(vPriorYear)'}, Date ={'<=$(vPriorYearDate)'}>} Actual_Amount)/Factor)/Sum({<FiscalYear = {'$(vMaxYear)'}, Date = {'<=$(vMaxDate)'}>} Actual_Amount)/Factor)
Hi Vinesh,
Can you help me out how to create YTD & PYTD flag in script level & that should be fiscal year flag. how can i take both flags in field.
The Attached App has both Standard and Fiscal Calendars
Your Finals Expression would be
//PS: in Script i've considered financial year ending thats why fin year 2015-2016 is YTD-2016 and 2016-2017 is YTD-2017
SUM({<Period = {'YTD-2016','YTD-2017'}>}Actual_Amount)/Factor
(SUM({<Period = {'YTD-2017'}>}Actual_Amount) - SUM({<Period = {'YTD-2016'}>}Actual_Amount))
/ SUM({<Period = {'YTD-2016'}>}Actual_Amount)
Thank you so much Vinesh. It works