topic Set Analysis in QlikView

Set Analysis

Fri, 08 May 2009 17:19:45 GMT

Hi, I have just started with this package so this may be a real easy question to answer.

I am using version 8.50.62315 and have the following code which previously worked but has stopped and I am not sure why.

Sum

({$<Date = {'>$(=date(vFromDate-1,'DD/MM/YYYY'))<$(=date(vToDate+1,'DD/MM/YYYY'))'}FCode={"01"}>}OrdTradeTotal)

the vFromDate and vToDate are variables which are set using the calandar object. These are working as I can display them in a text field.

If I remove the data parts and just leave

Sum ({$<FCode={"01"}>}OrdTradeTotal) . Set analysis does work. so its a problem with the dates I think

Can anyone spot a problem with the formula.

Thanks in advance.

Alan

Set Analysis

Fri, 08 May 2009 17:49:42 GMT

Hi,

I am not sure about this but I use to have errors because of the formating. So instead of comparing Date with (date(cFromDate) try to compair numbers.

Regards,

John

Set Analysis

sparur — Fri, 08 May 2009 19:15:01 GMT

Hi, Alan.

Try that:

Sum ({$<Date = {">$(=date(vFromDate-1,'DD/MM/YYYY'))<$(=date(vToDate+1,'DD/MM/YYYY'))"}, FCode={"01"}>} OrdTradeTotal)

best regards, sparur

Set Analysis

Tue, 09 Jun 2009 18:47:00 GMT

Hi Sparur,

Sorry for the delay in replying, work stopped access to the website (it had the word forums in the address) even though its for work...took a while to get them to re-instate access.

Unfortunately your suggestion didnt work, I think there is something more fundamentaly wrong with the Qlikview set-up as it was working and then stopped for no reason.

I'll let you know if I solve it.

Regards

Alan

Set Analysis

sparur — Tue, 09 Jun 2009 19:07:00 GMT

Hi, Alan.

It's very strange situation.

could you give an example of your application?

And what data type your date fields? as far as I remember, in order to work Set Analysis, they must numeric

Set Analysis

Tue, 09 Jun 2009 21:29:15 GMT

Alan ,

Try to put the # sign in front of your variables

exemple : #vFromDate instead of vFromDate.

your foemula should look :

({$<Date = {'>$(=date(#vFromDate-1,'DD/MM/YYYY'))<$(=date(#vToDate+1,'DD/MM/YYYY'))'}FCode={"01"}>}OrdTradeTotal)

You should not have to use the date function at that point.

Here is what I would use :

sum({$<Date > {$(#=only(vFromDate)-1)>} {$<Date < {$(#=only(vToDate) +1)>} FCode={"01"}>} OrdTradeTotal)

Philippe

Set Analysis

Tue, 09 Jun 2009 23:44:03 GMT

Hi Philippe,

Thanks for your input and thankyou to all the others that made suggestions. The problem is now solved, it was a very simple solution and was basically user error. I noticed that the SET TimestampFormat='DD/MM/YYYY' actually had 00:00:00 for hours minuts seconds so removed these, re-imported and hey presto.

Simple solution but had me going for a while.

Again thanks all who tried to help.

Kind Regards

Alan

Set Analysis

Tue, 09 Jun 2009 23:49:52 GMT

When i work with set analisis, i try to put all the complexity inside the variable, an then just put the variable "$(#vLastYear) i.e.", in the expression.