topic How do I divide a current month's value by previous months value in QlikView

How do I divide a current month's value by previous months value

Mon, 26 Jun 2017 00:05:23 GMT

I am trying to create a bar chart that shows a current month/previous month value fr each month. right now I have in my dimensions month and the expense type. I have already created an expression that will limit my expense type to the correct value, sum({<EXPENSE_TYPE = {'EBITDA'}>} AMOUNT). what I am trying to do now is show, for each month, the calculated values of that month divided by the previous month, do I need to use month(today()-1)/month(today()-1,-1) as part as part of the expression, and if so, how do I write the rest of it?

Mike

Re: How do I divide a current month's value by previous months value

Anil_Babu_Samineni — Mon, 26 Jun 2017 01:04:38 GMT

In simple, We can use Above() function

sum({<EXPENSE_TYPE = {'EBITDA'}, Month = {'$(=Max(Month))'}>} AMOUNT) / sum({<EXPENSE_TYPE = {'EBITDA'}, Month = {'$(=Max(Month)-1)'}>} AMOUNT)

sum({<EXPENSE_TYPE = {'EBITDA'}, Month = {'$(=Max(Month))'}>} AMOUNT) / sum({<EXPENSE_TYPE = {'EBITDA'}, Month = {'$(=AddMonths(Max(Month),-1))'}>} AMOUNT)

Re: How do I divide a current month's value by previous months value

sunny_talwar — Mon, 26 Jun 2017 01:42:24 GMT

May be try this

Aggr(Sum({<EXPENSE_TYPE = {'EBITDA'}>} AMOUNT)/Above(Sum({<EXPENSE_TYPE = {'EBITDA'}>} AMOUNT)), EXPENSE_TYPE, Month)

But the above expression will depend on the sort order of your Month field. If it isn't sorted in the ascending order in the script, you will run into issues.

If you are using the chart for only one type of expense_type, why even add it as a dimension.... may be try this

Dimension

Month

Expression

Sum({<EXPENSE_TYPE = {'EBITDA'}>} AMOUNT)/Above(Sum({<EXPENSE_TYPE = {'EBITDA'}>} AMOUNT))

Re: How do I divide a current month's value by previous months value

Mon, 26 Jun 2017 13:31:50 GMT

OK that didn't do it, it is just giving me values from 0-1 for all months, also I want to make sure that I can get this comparison to work for all months, not just the current months.