Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
harindersingh
Contributor III
Contributor III

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
harindersingh
Contributor III
Contributor III
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 II
Champion II

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

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)

harindersingh
Contributor III
Contributor III
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

vinieme12
Champion II
Champion II

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)

   

harindersingh
Contributor III
Contributor III
Author

Thank you so much Vinesh. It works