topic Re: Set Analysis, variable and deduction in QlikView

Set Analysis, variable and deduction

Fri, 03 May 2013 13:53:09 GMT

Hi,

I have an input box in which users can add a date (vComparableDate). I compute the sales for the day (using set analysis) belonging to the input date as follows:

=SUM({1<Sales_DATE_NEW={'$(=vComparableDate)'}>} Sales)

I want to compute the sales belonging to vComparableDate-1 for the previous day, but unfortunately I don't know the relevant syntax and cant find it either.

Can anyone help me out?

If only it was this easy:

=SUM({1<Sales_DATE_NEW={'$(=vComparableDate)-1'}>} Sales)

Regards,

Daniel

Re: Set Analysis, variable and deduction

alexandros17 — Fri, 03 May 2013 14:08:22 GMT

All dates can be transformed into numbers with "num" function

if you add -1 to the number obtained you are in "yesterday" ... then transform it again in date so:

date(num(vComparableDate)-1) could work ... check syntax ...

Hope it helps

Re: Set Analysis, variable and deduction

Fri, 03 May 2013 14:21:44 GMT

Hey Alexandros,

thanks for the suggestion but unfortunately I can't get it to work.

I've tried many ways, among others:

=SUM({1<Sales_DATE_NEW={'$(Num(vComparableDate)-1)'}>} Sales).

Re: Set Analysis, variable and deduction

alexandros17 — Fri, 03 May 2013 14:36:25 GMT

something like this

SUM({1<Sales_DATE_NEW={'$(=Date(Num($(vComparableDate))-1),'dd/MM/yyyy')'}>} Sales)

Re: Set Analysis, variable and deduction

Fri, 03 May 2013 15:15:20 GMT

Unfortunately it didn't work.

Perhaps I need to explain more:

on certain days (black sunday, easter and so on) sales will skyrocket.

I want to offer the functionality to type a certain date in an input box, and see how much the sales on that day went up or down compared to the previous day. (I will also use this figure for forecasting purposes later on).

Unfortunately, the input box behaves strangely. If I put in 02/03/2012 I can compute =SUM({1<Sales_DATE_NEW={'$(=vComparableDate)'}>} Sales) and it will return the sales for 02/03/2012. However if I use a num function it will divide 2 by (3*2012), giving a very low figure. Recasting that num to a date will return a date in the 19th century..

So before I can even continue I need to have a reliable way to cast "human" dates to a num...

This is getting more complicated than I initially thought..

Re: Set Analysis, variable and deduction

Fri, 03 May 2013 17:36:19 GMT

Hi,

You need to serialise your dates. Create a new field in the script for the table in question. The expressio for the new field will be

num(Sales_DATE_NEW) as SalesDateNewSerial .

You then need to create 2 variables one to capture the user input and one to manipulate this value into the one you require.

The $(varibleName) will try and evaluate what ever is inside the 2 brackets. So if varibleName is = 23/12/2012 it will divide 23 by 12 then by 2013 giving you a small number.

See the attached application for more details.

Kind Regards,

Matt.

Re: Set Analysis, variable and deduction

Fri, 03 May 2013 17:57:33 GMT

Hi Matt,

the problem isn't in the dates, they are serialized.

Rather I want to offer the user the opportunity to set a range of parameters (such as a date for comparison, number of days and so on). These parameters will then be used to perform a range of operations on the data (serialized, clean).

The problem is that I can't get the input box to translate a date a user would enter (5/3/2013) to a serialized date.

Of course I can offer the user the opportunity to select a date through a listbox, and then set a variable as the last date in the selection (offering the same functionality). However this is not the preferred solution because the user would need to enter the date in a listbox and other parameters in an input box.

How do you typically allow users to set parameters for your applications?

Kind regards,

Daniel

Re: Set Analysis, variable and deduction

Fri, 03 May 2013 18:16:55 GMT

Hi Daniel,

In my test file attached I have 2 varibles one takes the user input and the other manipulates this value by subtracting one day off it. It is this second varible that is used in the set anaysis equation. Open up the varible expressions from the Settings>Varible Overview. You will see the what I am doing.

KR Matt.

Re: Set Analysis, variable and deduction

Mon, 06 May 2013 10:57:47 GMT

Thanks a lot Matt!

I ended up having to create way more variables than I wanted, but at least this is workable.

Kr,

Daniel