topic Re: Previous 'N' Month calculation in App Development

Previous 'N' Month calculation

Dhineshwaran — Mon, 20 Dec 2021 21:08:46 GMT

Hi,

The requirement is, when I select a particular month say 'March' for an year say '2020', All the previous months must also be displayed say 'Jan','Feb' and 'March'. How could i achieve this? Please help me out.

Re: Previous 'N' Month calculation

Saravanan_Desingh — Tue, 30 Jun 2020 03:28:09 GMT

Where do you want to show? In a Chart?

Re: Previous 'N' Month calculation

Dhineshwaran — Tue, 30 Jun 2020 03:31:14 GMT

In a bar chart as well as in a table view. Once I select the parameter, I need this to reflect in both the analysis.

Re: Previous 'N' Month calculation

Vegar — Tue, 30 Jun 2020 05:56:48 GMT

You can accomplish this by introducing set analysis into your chart and table expression.

The set will differ based on how you want the logict to be, but your header states "Previous 'N' months" so try something like this.

Assuming your original expression is SUM([Amount]) and N=4. I also you have four calendar fields Year, Month, Date and YearMonth that you need to ignore in order to get the expression right. With these assumptions an expression could look like this.

Sum({<Year, Month, YearMonth, Date = {">=$(=addmonths(max(Date),-4))<=$(=max(Date))"}>}[Amount])

Re: Previous 'N' Month calculation

Dhineshwaran — Wed, 01 Jul 2020 10:13:23 GMT

The expression is not working sir.

My Requirement is , When i select the Year is 2020 and Month is May in filter pane, the line chart should display Jan to May.if I select AUG,the line chart should display 'JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG'.

I want to display like this in table and line chart.

Re: Previous 'N' Month calculation

NitinK7 — Wed, 01 Jul 2020 11:56:36 GMT

Hi,

Try

dimension- Month

exprssion- sum({<month={"<=$(=max(month))"}>}premium)

Re: Previous 'N' Month calculation

Dhineshwaran — Mon, 06 Jul 2020 10:14:15 GMT

Hi Nitin,

I have tried the expression but its not working .

My Expression is

sum({<MONTH_NAME={"<=$(=max(MONTH_NAME))"}>}[XXBI_GL_GROUP_BALANCE_V.PTD_VALUE])

KIndly advise on this.

Regards,

Dhinesh

Re: Previous 'N' Month calculation

NitinK7 — Mon, 06 Jul 2020 10:25:25 GMT

hi,

can you attach you data for this two field, it is easy to find solution

Re: Previous 'N' Month calculation

Vegar — Mon, 06 Jul 2020 14:24:18 GMT

Set modifiers always look for the text of a dual when comparing a value. (Assuming your field is a dual value with month name and month number, probably created by using the Month() function)

Max(MONTH_NAME) will return a numeric value of your dual value and you will have no match. Try replacing max with maxstring() like this.

sum({<MONTH_NAME={"<=$(= MaxString(MONTH_NAME))"}>}[XXBI_GL_GROUP_BALANCE_V.PTD_VALUE])

Re: Previous 'N' Month calculation

Dhineshwaran — Tue, 14 Jul 2020 12:36:34 GMT

Hi,

Apologies, tried something like this but not successful.

sum({<MONTH_NAME={"<=$(= MaxString(MONTH_NAME))"},[XXBI_GL_GROUP_BALANCE_V.DISPLAY_FLAG]= {"Y"},
[XXBI_GL_GROUP_BALANCE_V.ACCT_GROUP_NAME] = {"Revenue"}>}[XXBI_GL_GROUP_BALANCE_V.PTD_VALUE]).

Regards,

Dhinesh

Re: Previous 'N' Month calculation

Dhineshwaran — Sun, 19 Jul 2020 12:08:48 GMT

Hi Vegar,

Can you help me to sort out this issue?

Regards,

Dhinesh

(+91) 9894024763

Re: Previous 'N' Month calculation

Vegar — Wed, 22 Jul 2020 08:46:19 GMT

Check out the attached qvf file.

In top chart I'm using

=sum({< //[YEAR] = {'$(=Max(YEAR))'}, [MONTH]={"<=$(=Max(MONTH))"}, [XXBI_GL_GROUP_BALANCE_V.DISPLAY_FLAG]= {"Y"}, [XXBI_GL_GROUP_BALANCE_V.ACCT_GROUP_NAME] = {"Revenue"}>} [XXBI_GL_GROUP_BALANCE_V.PTD_VALUE])

In the bottom chart

=sum({< [YEAR] = {'$(=Max(YEAR))'}, [MONTH]={"<=$(=Max(MONTH))"}, [XXBI_GL_GROUP_BALANCE_V.DISPLAY_FLAG]= {"Y"}, [XXBI_GL_GROUP_BALANCE_V.ACCT_GROUP_NAME] = {"Revenue"}>} [XXBI_GL_GROUP_BALANCE_V.PTD_VALUE])

Notice that when logical comparing using <= on [MONTH] I use Max() not MaxString() as I don't want to logical compare the string value.