topic Re: Calculated dimension for 6 month from Max date. in QlikView

Calculated dimension for 6 month from Max date.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculated dimension for 6 month from Max date.

Anil_Babu_Samineni — Mon, 24 Jul 2017 10:10:37 GMT

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)

Re: Calculated dimension for 6 month from Max date.

mangalsk — Mon, 24 Jul 2017 10:11:32 GMT

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

Re: Calculated dimension for 6 month from Max date.

Anonymous — Mon, 24 Jul 2017 10:16:06 GMT

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.

Anil_Babu_Samineni — Mon, 24 Jul 2017 10:25:17 GMT

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)

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

Re: Calculated dimension for 6 month from Max date.

Mon, 24 Jul 2017 10:25:25 GMT

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)

Re: Calculated dimension for 6 month from Max date.

Anonymous — Mon, 24 Jul 2017 11:19:09 GMT

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.

Re: Calculated dimension for 6 month from Max date.

mangalsk — Mon, 24 Jul 2017 11:20:58 GMT

Can you attached qvw with some dummy data

Re: Calculated dimension for 6 month from Max date.

Anil_Babu_Samineni — Mon, 24 Jul 2017 11:21:58 GMT

Can you please share application that may trigger your issue

Re: Calculated dimension for 6 month from Max date.

rammuthiah — Mon, 24 Jul 2017 11:37:16 GMT

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

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

Re: Calculated dimension for 6 month from Max date.

Anonymous — Mon, 24 Jul 2017 18:10:35 GMT

Hi, Please find the attached app, this app has two charts first one show today+ 6 months data.

Second one is need to show last six months from end date.

Thanks in advance@Anil Babu Samineni