Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
somspalanisamy
New Contributor III

Calculated dimension for 6 month from Max date.

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.

Tags (1)
10 Replies

Re: Calculated dimension for 6 month from Max date.

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
mangalsk
Contributor III

Re: Calculated dimension for 6 month from Max date.

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

somspalanisamy
New Contributor III

Re: Calculated dimension for 6 month from Max date.

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

Re: Calculated dimension for 6 month from Max date.

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable

Re: Calculated dimension for 6 month from Max date.

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)

somspalanisamy
New Contributor III

Re: Calculated dimension for 6 month from Max date.

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
Contributor III

Re: Calculated dimension for 6 month from Max date.

Can you attached qvw with some dummy data

Re: Calculated dimension for 6 month from Max date.

Can you please share application that may trigger your issue

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rammuthiah
Contributor II

Re: Calculated dimension for 6 month from Max date.

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

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