topic Error in calculation? in QlikView

Error in calculation?

richnorris — Mon, 28 Apr 2014 21:08:49 GMT

Hi, can anyone see what I'm doing wrong here? I have a calculated dimension of:

=if(Week < Week(Max([Trade Date]) ), Week)

Which should show all weeks other than the week of the maximum trade date, right? This isn't what I'm trying to achieve overall, so I'm not looking for alternatives, just why this peice of code isn't working?

Re: Error in calculation?

MK_QSL — Mon, 28 Apr 2014 21:19:58 GMT

Instead of Calculated Dimension

Use

Dimension = Week

Expression

Something Like

SUM({<Week = {'$(=Week(Max([Trade Date])))'}>}Sales)

Re: Error in calculation?

richnorris — Mon, 28 Apr 2014 21:22:35 GMT

So you're saying its impossible to fix that code to work?

Re: Error in calculation?

MK_QSL — Mon, 28 Apr 2014 21:42:57 GMT

not like that...

this is alternate suggestion.

if you upload you sample file or apps, we can see what exactly wrong with your calculated dimension.

Re: Error in calculation?

swuehl — Mon, 28 Apr 2014 22:09:33 GMT

I think you would need to use advanced aggregation (aggr() function) in your calculated dimension if you want to use an aggregation function.

But it's probably easier if you define

=max([Trade Date])

in a variable vMaxTradeDate, then use this in the calculated dimension:

=if(Week < Week(vMaxTradeDate), Week)

(and check if [Trade Date] is a date with a numeric representation, so max() will return a value)

Re: Error in calculation?

rwunderlich — Mon, 28 Apr 2014 22:19:37 GMT

I think you just need to add the TOTAL qualifier.

=if(Week < Week(Max(TOTAL [Trade Date]) ), Week)

-Rob