topic Re: Calculated Dimension Error in QlikView

Calculated Dimension Error

Mon, 24 Aug 2015 15:02:48 GMT

Hi, I'm really struggling with something that seems like it should be easy, hope one of you clever people out there can help...

I'm trying to create a calculated dimension, which works perfectly if I put it in a text box and the dimensions work perfectly on their own.

=if(month(max(IMPORT.SOLD_MMMYY)) - month(TODAY()) = -1, SOLD.YOYPeriod, SOLD.YOYPeriod2Mnths)

I can't figure out what I'm doing wrong, please help, thanks

Re: Calculated Dimension Error

ChiragPradhan — Mon, 24 Aug 2015 15:08:57 GMT

Seems like an issue with date formats. Check both dates are correct format.

Re: Calculated Dimension Error

sunny_talwar — Mon, 24 Aug 2015 15:10:40 GMT

I think since you are using Max() function here, you might need an aggregate function here. Won't really say much before knowing what you are trying to do. Can you elaborate what you are trying to do with your dimension?

Re: Calculated Dimension Error

Mon, 24 Aug 2015 15:12:45 GMT

Hi all dates are the same format, what's confusing me is that if I copy the expression above into a text box and select certain dates from the calendars, the if statement works correctly and returns me the correct value.

Re: Calculated Dimension Error

Mon, 24 Aug 2015 15:45:18 GMT

Hi Sunny, what I'm trying to do is select which dimension to use, this is determined as:

if IMPORT.SOLD_MMMYY is one month behind the current month then use SOLD.YOYPeriod as the dimension,

otherwise

if IMPORT.SOLD_MMMYY is two months behind the current month then use SOLD.YOYPeriod2Mnths as the dimension.

Thanks, Simon

Re: Calculated Dimension Error

Mon, 24 Aug 2015 16:04:05 GMT

sorry, if the latest IMPORT.SOLD_MMMYY

Re: Calculated Dimension Error

ChiragPradhan — Mon, 24 Aug 2015 16:57:09 GMT

Find attached a sample. Just bear in mind, its best to avoid calculated dimensions for large data.

Re: Calculated Dimension Error

ramoncova06 — Mon, 24 Aug 2015 17:02:16 GMT

I agree with Sunny since you are using an aggregation in your calculated dimension, you need to use the function aggr

=if(month(aggr(max(IMPORT.SOLD_MMMYY),IMPORT.SOLD_MMMYY)), - month(TODAY()) = -1, SOLD.YOYPeriod,SOLD.YOYPeriod2Mnths)

Re: Calculated Dimension Error

joe_nguyen — Tue, 27 Oct 2015 15:37:27 GMT

Simon, I am late to this discussion, but I would add my solution just in case.

One of the most important rules for Calculated Dimension is that you can not used aggregation functions (sum, max, min etc). This is why your expressions worked perfectly in text boxes, but failed in Calculated dimension. To get around that limitation, you have to put all aggregation functions inside an aggr() one.

I believe your expression would work if written as follow:

=if(month(aggr(nodistinct max(IMPORT.SOLD_MMMYY), Year)) - month(TODAY()) = -1, SOLD.YOYPeriod,SOLD.YOYPeriod2Mnths)