Previous 'N' Month calculation

[Dhineshwaran]

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.

[Saravanan_Desingh]

Where do you want to show? In a Chart?

[Dhineshwaran]

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.

[Vegar]

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])

[Dhineshwaran]

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.

[NitinK7]

Hi,

Try

dimension- Month

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

[Dhineshwaran]

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

[NitinK7]

hi,

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

[Vegar]

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])

[Dhineshwaran]

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