Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Percentage calculation current & last year till date in Bar char

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

5 Replies
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

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)

Anonymous
Not applicable
Author

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.

vinieme12
Champion III
Champion III

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)

   

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Thank you so much Vinesh. It works