Calculated dimension for 6 month from Max date.

[Anonymous]

Hello All,

I need a help to show last six months data from max date. I have a data from Jan-2015 to  Dec- 2019. I want show the Jun 2019 to Dec-2019 using Bar chart. i need calculated dimension to show.  Would really appreciate your help.

[Anil_Babu_Samineni]

I don't prefer to use Calc. Dimension and it kills performance. If so, you can use in script too

May be this in Set analysis? I assume, you have good Date format.

Sum({<Months = {">=$(=AddMonths(Max(Months),-6)) <=$(=Max(Months))"}>}Sales)

If not, Use this?

Sum({<Months = {">=$(=Date(AddMonths(Max(Months),-6),'MMM YYYY')) <=$(=Date(Max(Months),'MMM YYYY'))"}>}Sales)

[mangalsk]

Hello take month in dimension and in expression you can write set analysis like

sum({<Month={"<=Month(Today())>=Month(AddMonths(Today(),-5))"}>}sales)

this is just an idea , you may need to modify expression.Try if not works send qvw

[Anonymous]

Thanks for you r replay.

I am not using any aggregate functions. i have using the below for first 6 months from today.

=if(vdate <= today()+150 and vdate  > today(), date(vdate,'MMM-YY'))

like this, I need to show the data  from max - 6 month

[Anil_Babu_Samineni]

I wonder, Why do you need this without Aggregation functions. Some how, For your question may be use this for Months as Calc. Dim ??

only({<Date={">=$(=AddMonths(Months,-6))) <= $(=Max(Months))"}>} Months)

OR

aggr(only({<Date={">=$(=AddMonths(Max(Months),-6)) <= $(=Max(Months))"}>} Months),Months)

[#6]

Hi consider this. Replace POSTING_DATE with the date parameter you have.Month, Day, Quarter are the parameters to give Month, day and Quarter selection. Kindly ignore Month=,Day=,Quarter= if you don't want

Sum({<Calender_Date ={'>=$(=Monthstart(max(POSTING_DATE),-5)) <=$(=max(POSTING_DATE))'},POSTING_DATE=,

Month=,Day=,Quarter=>} sales)

[Anonymous]

Not working. i want to show last six months from Max date.

=if(End_date <= today()+150 and End_date  > today(), date(End_date,'MMM-YY'))

the dimension returns only last six months from the end date. If the last date is Dec- 2019. Then, i need to show Jun-2019 to Dec-2019. month as a dimension.

[mangalsk]

Can you attached qvw with some dummy data

[Anil_Babu_Samineni]

Can you please share application that may trigger your issue

[rammuthiah]

Use this expression and date field should be converted into month format

sum({<Month={'>=$(=only(Month)-6)<=$(=only(Month))'}>} sales)