Solved: Dimesnion % Calculation - Qlik Community

waqasshah · ‎2018-12-20

Dear Experts,

I need your help. Actually I need to calculate the percentage between the dimension columns. Please review the attached picture. The top 2 percentage calculations marked with red sign are my desired results. Can anyone please tell me how to do these calculation in chart or on a text box ?

Thanks in advance

Regards

Waqas Shah

sunny_talwar · ‎2018-12-20

Check attached...

Script changes

ActivitySales:
LOAD *,
	 SubField(ActivityFinancialYear, '/', 2) as Year;
LOAD * INLINE [
    SalesItem, ActivityPrice, ActivityDate, ActivityMonth, ActivityFinancialYear
    1, 100, 01-01-2015, January, 2015/2016
    2, 200, 20-01-2015, January, 2015/2016
    1, 150, 15-02-2015, February, 2015/2016
    2, 210, 22-02-2015, February, 2015/2016
    1, 250, 01-03-2015, March, 2015/2016
    2, 270, 10-03-2015, March, 2015/2016
    2, 300, 19-04-2016, April, 2016/2017
    2, 600, 13-04-2016, April, 2016/2017
    2, 970, 25-05-2016, May, 2016/2017
    2, 120, 28-05-2016, May, 2016/2017
    2, 330, 03-06-2016, June, 2016/2017
    2, 650, 06-06-2016, June, 2016/2017
    2, 170, 07-07-2017, July, 2017/2018
    2, 960, 09-07-2017, July, 2017/2018
    2, 150, 10-08-2017, August, 2017/2018
    2, 235, 11-08-2017, August, 2017/2018
    2, 344, 14-09-2017, September, 2017/2018
    2, 496, 15-09-2017, September, 2017/2018
    2, 483, 06-10-2018, October, 2018/2019
    2, 798, 27-10-2018, October, 2018/2019
    2, 101, 23-11-2018, November, 2018/2019
    2, 502, 30-11-2018, November, 2018/2019
    2, 659, 19-12-2018, December, 2018/2019
    2, 710, 03-12-2018, December, 2018/2019
];

Expression

=Num(Sum({<Year = {"$(=Max(Year))"}>} ActivityPrice)/Sum({<Year = {"$(=Max(Year)-1)"}>} ActivityPrice)-1, '##.#%')

View solution in original post

sunny_talwar · ‎2018-12-20

What is your expression within the chart? All you need to do is something like this

Sum({<Year = {"$(=Max(Year))"}>}Measure)/Sum({<Year = {"$(=Max(Year)-1)"}>}Measure)

and

Sum({<Year = {"$(=Max(Year)-1)"}>}Measure)/Sum({<Year = {"$(=Max(Year)-2)"}>}Measure)

All you need is a year field which is numeric field and correlates to your fiscal year

waqasshah · ‎2018-12-20

Thanks I will try.. following is the information

Dimension = ActivityFinancialYear

Values in DB for ActivityFinancialYear = '2015/2016', '2016/2017' , '2018/2019'

Expression for Sum of Activities

=NUM(sum(Activity),'#,###.','.',',' )

sunny_talwar · ‎2018-12-20

Like I suggest, may be create a new field using ActivityFinancialYear which can be used in set analysis (or you may already have it)... but if you don't... create it like this may be

SubField(ActivityFinancialYear, '/', 2) as Year

and then just this

Sum({<Year = {"$(=Max(Year))"}>} Activity)/Sum({<Year = {"$(=Max(Year)-1)"}>} Activity)

waqasshah · ‎2018-12-20

Thanks I will try it. Please note I am using Bar chart not Pivot Chart.

Secondly If you see the screenshot I want to display percentages on top of the bars. So it means I am using text boxes for the percentage calculation or labels in expression. Thanks in advance.

sunny_talwar · ‎2018-12-20

Noted 🙂

waqasshah · ‎2018-12-20

Thanks but I am asking.. my approach is right

sunny_talwar · ‎2018-12-20

Oh, I am sorry. I thought you are just telling me what you have. What exactly is your question for me?

I see these

Please note I am using Bar chart not Pivot Chart.

Okay, Bar chart or Pivot chart... how does it matter if we are doing the expression in a text box object 🙂

Secondly If you see the screenshot I want to display percentages on top of the bars.
So it means I am using text boxes for the percentage calculation or labels in expression.

Again, if you are using the expressions I provided in the text box object... they should work... give it a try and see if they do or not. 🙂

Is there another question for me?

waqasshah · ‎2018-12-20

Many Thanks Sunny.. you are always very helpful.

Let me try If any question I will let you know. Thanks again...

waqasshah · ‎2018-12-20

Sorry Sunny its not working.. Please review the attached sample file