topic trouble with building a setanalysis in QlikView

trouble with building a setanalysis

Mon, 21 May 2012 10:23:46 GMT

Hello,

I'm having some trouble with building a setanalysis in qlikview

I want to compare two dates in sales with the min and max.

This is the syntax i'm using now:

max

sum

( {<date = {$(=max([date])-1) }>} sales)

min

sum

( {<date = {$(=min([date])-1) }>} sales)

But as a result i get € 0,-

What am i doing wrong?

trouble with building a setanalysis

masha-ecraft — Mon, 21 May 2012 10:29:00 GMT

You might not have any sales registered on date prior to your maximum date (max(date)-1) and that is why you are getting 0 for the first expression.

The second expression calculates sum of sales for the date prior to minimum date (min(date)-1). Such date should not exist.

trouble with building a setanalysis

nilesh_gangurde — Mon, 21 May 2012 10:34:40 GMT

Hi Renes,

If Possible please attach the qvw file with the sample data.

Regards,

Nilesh Gangurde

Re: trouble with building a setanalysis

Miguel_Angel_Baeyens — Mon, 21 May 2012 10:39:01 GMT

Hi,

Max() always return a numeric value, while my guess is that the field "date" has a non numeric value ("21/05/2012") so you will need to format it in the set analysis functions, for example:

Sum({< date = {'$(=Date(max([date])-1))'} >} sales)

Anyway, try in a text object that the result of the function is the same as the format in the "date" field. Note as well that in QlikView fields are case sensitive, so "Date" is not the same as "date".

Hope that helps.

Miguel

Re: trouble with building a setanalysis

Mon, 21 May 2012 12:44:30 GMT

Hello Nilesh, hereby the qvw.

Thank you for your help.

Re: trouble with building a setanalysis

Mon, 21 May 2012 14:40:51 GMT

Hello Nilesh, did you find the error?

Re: trouble with building a setanalysis

Miguel_Angel_Baeyens — Mon, 21 May 2012 14:58:09 GMT

Hi,

Did you try the solution I suggested below? That's working for me in your file.

Miguel

Re: trouble with building a setanalysis

Mon, 21 May 2012 15:01:16 GMT

Hello Miguel,i've tried your answer

Sum({< date = {$(=Date(max([date])-1)) } >} sales)

but that didn't work for me. What i'm trying to do is select the sales from yesterday and i don't know what i'm doing wrong. I have to say that i am new with qlikview developer.

Re: trouble with building a setanalysis

Miguel_Angel_Baeyens — Mon, 21 May 2012 15:03:28 GMT

Hi,

Then I recommend you to check this file that uses variables to get maximum and minimum dates, and uses set analysis.

Hope that helps.

Miguel

Re: trouble with building a setanalysis

Mon, 21 May 2012 15:12:56 GMT

Hello Miguel,you're example is much too difficult for me. I've just started yesteday with a selfstudy.

The only thing that i'm trying to do is: get the salesturnover from yesterday but i am not able to do so (even get today i cannot accomplish).

So the only thing i can produce in the definition is sum(sales). But how can i use a date within this formula?

Re: trouble with building a setanalysis

Miguel_Angel_Baeyens — Mon, 21 May 2012 15:20:57 GMT

Hi,

Check your amended file attached to my example below. Using single quotes (because dates are in a text format, not in numeric format) but based on your Max() -1 and Min() works.

Hope that helps.

Miguel

Re: trouble with building a setanalysis

nilesh_gangurde — Tue, 22 May 2012 13:24:04 GMT

Hi please find the Attachment.

Re: trouble with building a setanalysis

Wed, 23 May 2012 14:10:00 GMT

Hello Nilesh,

Thank you for your help, yes this one did the trick.

Regards,

Rene Santifort

Drogisterij.net, veilig en vertrouwd

Voor 21:00 uur besteld, morgen in huis

Re: trouble with building a setanalysis

nilesh_gangurde — Thu, 24 May 2012 05:33:53 GMT

heyy Renes

If you got the answer please mark the question as answered...

Regards,

Nilesh Gangurde

Re: trouble with building a setanalysis

Thu, 24 May 2012 05:59:22 GMT

Done.

Met vriendelijke groet,

Rene Santifort

Drogisterij.net, veilig en vertrouwd

Voor 21:00 uur besteld, morgen in huis

Re: trouble with building a setanalysis

nilesh_gangurde — Thu, 24 May 2012 06:01:34 GMT

Thanxxxx

Regards,

Nilesh Gangurde