topic Re: Why won't this work... in QlikView

Why won't this work...

samvile18 — Tue, 11 Dec 2012 10:44:22 GMT

...can anyone tell me why this won't work?

=sum({<Measure={'FUM'},FExtractDate={'$(=MonthEnd(MaxDate))'}>}Value)

MaxDate is a date field along with FExtractDate - I need to sum the FUM value where these two match.

Re: Why won't this work...

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 10:53:29 GMT

Hi,

Do both MonthEnd() and FExtractDate and MaxDate have the same exact format (i.e.: DD/MM/YYYY)? You can try with:

Sum({< Measure = {'FUM'}, FExtractDate = {'$(=Date(MonthEnd(MaxDate)))'} >} Value)

Hope that helps.

Miguel

Re: Why won't this work...

fdelacal — Tue, 11 Dec 2012 10:53:33 GMT

=sum({<Measure={'FUM'},FExtractDate={'$(=MonthEnd(MaxDate))'}>}Value)

try with " "

{"FUM"} {"$(=MonthEnd(MaxDate))"}

Hope it helps you

Re: Why won't this work...

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 11:03:45 GMT

Hi,

Single quoting in set analysis is OK as long as the value is a fixed, literal value. FUM is and so a date in a format DD/MM/YYYY. Although that will work, double quoting in set analysis is used with search values and accept wildcards, for example, to return a set where date ends with "2012":

Sum({< FExtractDate = {"*2012"} >} Value)

Hope that helps.

Miguel

Re: Why won't this work...

samvile18 — Tue, 11 Dec 2012 11:04:26 GMT

I can't get either of those answers to work...this really is puzzling me.

I have no idea why this is returning nothing.

Re: Why won't this work...

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 11:07:33 GMT

Hi,

Is MaxDate a field, a variable? Does it have the same format as FExtractValue?

Miguel

Re: Why won't this work...

samvile18 — Tue, 11 Dec 2012 11:09:23 GMT

MaxDate is a field I've created within the script

Re: Why won't this work...

samvile18 — Tue, 11 Dec 2012 11:10:32 GMT

It should have the same formatting as I've used the Date expression within the script for both.

Re: Why won't this work...

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 11:15:54 GMT

Hi,

Try then with Max() function:

Sum({< Measure = {'FUM'}, FExtractDate = {'$(=MonthEnd(Max(MaxDate)))'} >} Value)

Note that set analysis works once for the whole chart, and not line by line. In this case, you should use an If() conditional instead. Set analysis is intended when you want the expression to work on a different set than the one in the sheet, but not to make comparisons for each value in the dimension.

Hope that helps.

Miguel

Re: Why won't this work...

samvile18 — Tue, 11 Dec 2012 11:20:21 GMT

I think I'm going to have to go with an IF statement then...do you know how I would tell it to ensure the Measure is FUM along with making sure the dates are matching?

Re: Why won't this work...

Miguel_Angel_Baeyens — Tue, 11 Dec 2012 11:23:19 GMT

Sam,

Depending on the date values, it should be something like this

If(Measure = 'FUM' AND FExtractDate = MonthEnd(MaxDate), Sum(Value))

Sum(If(Measure = 'FUM' AND FExtractDate = MonthEnd(MaxDate), Value))

Hope that helps.

Miguel

Re: Why won't this work...

samvile18 — Tue, 11 Dec 2012 11:29:53 GMT

Neither of these are working so I'm beginning to think there must be an issue with the formatting of the dates