Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis, variable and deduction

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


1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

8 Replies
alexandros17

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

Not applicable
Author

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).

alexandros17

something like this

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

Not applicable
Author

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..

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.

View solution in original post

Not applicable
Author

Thanks a lot Matt!

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

Kr,

Daniel