topic Re: Dimension (Between Two Dates) in QlikView

Dimension (Between Two Dates)

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

Hi Guys,

I am trying to use the below statement in Calculated Dimension, just to show the dimension (within last 12 months). and have a sum function of sale in expression.

but im getting an error or displaying 0 dimension. could you please advise me about this?

=Aggr(if(([Year Month]<=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM') And [Year Month]<=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM')) , [Month Year]), [Month Year], Company)

Kind regards,

Farrukh

Re: Dimension (Between Two Dates)

jonas_rezende — Fri, 25 Nov 2016 17:40:46 GMT

Hi, Farrukh Shaikh.

Try of expression below:

Aggr(if(([Year Month]>=Date(MonthStart(AddMonths(Min(Date),-11)),'YYYYMM') And [Year Month]<=Date(Max(Date),'YYYYMM') ) , [Month Year]), [Month Year], Company)

P.S: be sure to check the format of the [Year Month] field if it is YYYYMM.

Hope this helps!

Re: Dimension (Between Two Dates)

Anonymous — Mon, 28 Nov 2016 10:07:13 GMT

Hi Jason,

I tried the above as suggested but I only want to display last 12 months sales record by dimension,

My current selection is year 2016 and month is Aug.

below is the table format.

[Month Year] , Region, Company , Sales

Could you please advise me how to show all the records by range dimension.

Kind regards,

Farrukh

Re: Dimension (Between Two Dates)

jonas_rezende — Mon, 28 Nov 2016 16:54:45 GMT

Hi, Farrukh Shikh.

Please, share your file qvw.

Jonas Melo.

Re: Dimension (Between Two Dates)

vikramv — Tue, 11 Dec 2018 19:36:32 GMT

Can you try like this ?

Dimensions

[Month Year]
Region
Company

Expressions
sum({<[Month Year]= {">=$(=date(MonthName(AddMonths(max([Month Year]),-11 ),'MMMYYYY'))) <=$(=date(MonthName(AddMonths(max([Month Year]),0 ),'MMMYYYY')))"}>})