topic Re. :Daily Sales in QlikView

Daily Sales

Thu, 06 May 2010 17:11:22 GMT

Does something look wrong with this formula?

sum({<TotNumber={'60','61','62'},SalesDate={'05/05/2010'}>}if(isnull(EndDate),Amount))

This formula works, but I also want to select by date.

sum({<TotNumber={'60','61','62'}>}if(isnull(EndDate),Amount))

My main goal is to develop a report that shows Daily Sales in columns so I can see the previous 7 days worth of sales.

Re. :Daily Sales

martin59 — Thu, 06 May 2010 19:30:29 GMT

I don't see any error.

Could you send an exemple of your application to illustrate this. You can reduce your datas and scramble them in document properties / scrambling if you want.

Martin

Re. :Daily Sales

johnw — Thu, 06 May 2010 21:24:31 GMT

In case it matters, QlikView is sensitive to the date format used in set analysis. So the '05/05/2010' will match a SalesDate in 05/05/2010 format, but not 05/05/10 format, for instance. Also, I recommend len(EndDate)<1 instead of isnull(EndDate) due to some implementation differences with, I believe, the 64-bit version. But no, I'm not actually seeing an error.

Re. :Daily Sales

Thu, 06 May 2010 22:53:45 GMT

5/5/2010 12:00:00AM

this is the actual format that is pulled in another application, I tried to type this exactly and it still doesn't work. Is it possible to set the format of the Date in the set analysis formula?

Re. :Daily Sales

johnw — Thu, 06 May 2010 23:11:48 GMT

Yes, you can set the format of the date in the set analysis expression. For instance, I think this:

sum({<TotNumber={'60','61','62'},SalesDate={'$(=date(date#(20100505,'YYYYMMDD'),'D/M/YYYY hh:mm:ss TT'))'}>}if(isnull(EndDate),Amount))

BUT that isn't what I'd do. Instead, I'd convert your date to your desired format as your read it into QlikView from your data source. Something like this:

date(date#(SourceSalesDate,'D/M/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as SalesDate

Since SalesDate will then be in the desired format, I believe your original expression would then work as is.

Re. :Daily Sales

Fri, 07 May 2010 16:16:14 GMT

Ok, I got the formula to work as follows.

sum

In order to see the last 7 days worth of sales though I have to have 7 expressions with different dates in each expression. It works, but I have to change the date every day in each expression I would like to try to get it to calculated autimatically.

I've tried substituting the following in for the date SalesDate={'Today()-1'} and SalesDate={'Max(SalesDate)'}. but the formulas don't work then.

({<TotNumber={'60','61','62'},SalesDate={'5/2/2010 12:00:00 AM'}>} Amount)

Re. :Daily Sales

Fri, 07 May 2010 16:17:45 GMT

Ok, I got the formula to work as follows.

sum({<TotNumber={'60','61','62'},SalesDate={'5/2/2010 12:00:00 AM'}>} Amount)

I've tried substituting the following in for the date SalesDate={'Today()-1'} and SalesDate={'Max(SalesDate)'}. but the formulas don't work then.

Re. :Daily Sales

Fri, 07 May 2010 16:33:05 GMT

If you place your code or formulas between the words code and /code both enclosed in square brackets [ ] you get the nice gray box with fixed font and scroll bar ...

You have to inclose your date value in a $ expansion expression


Sum({<TotNumber={'60'},SalesDate={'$(=Max(SalesDate))'}>} Amount)

You should also make sure you use the same date format throughout your app.

Re. :Daily Sales

Mon, 10 May 2010 19:56:41 GMT

Thanks, that formula works as I want it to. Now I want to create another column with the daily sales for the day before the date calculated previously. I tried adding -1 after $(=Max(SalesDate)) but it doesn't work. What's the trick?

Re. :Daily Sales

johnw — Mon, 10 May 2010 20:18:25 GMT

I believe this would be the right syntax, but have not tested.

sum({<TotNumber={'60'},SalesDate={'$(=date(max(SalesDate)-1))'}>} Amount)

Re. :Daily Sales

Mon, 10 May 2010 20:35:34 GMT

That didn't work either. I created another column in the application with the expression =Max(SalesDate)-1 and it returns the value I'm looking for, but once I include it in the expression to sum the sales for that day it doesn't work!

Re. :Daily Sales

johnw — Mon, 10 May 2010 20:40:56 GMT

You may have to manually specify a date format:

sum({<TotNumber={'60'},SalesDate={'$(=date(max(SalesDate)-1,'your format here'))'}>} Amount)

You may have to list any date-like fields that are connected to your SalesDate, telling the system to ignore them like this:

sum({<TotNumber={'60'},SalesDate={'$(=date(max(SalesDate)-1))'},SalesWeek=,SalesMonth=,SalesYear=>} Amount)

It's hard to debug without knowing anything else about your data model or script, but perhaps one or the other or both of those will be required in your case.

Daily Sales

james — Mon, 10 May 2010 20:55:12 GMT

Radam, in our sales world we are using this to retrive last 7 days...

-sum({<Year=,Date={">=$(=date((vToday -1)-7,'MM/DD/YY'))"} > } [Sales])

You than can throw in date as a column and it will show you sales by said date

Re. :Daily Sales

Mon, 10 May 2010 21:50:47 GMT

What info can I tell you to help troubleshoot? I'm selecting data from 2 tables both tables have other date fields, does that mean I need to tell the system to ignore them?

Also, If I need to manually specify a date format would it be the same format that is set at the beginning of the script?

Re. :Daily Sales

johnw — Mon, 10 May 2010 23:11:47 GMT


radam wrote: I'm selecting data from 2 tables both tables have other date fields, does that mean I need to tell the system to ignore them?

I can only tell you how to think the problem though, not what the answer is.

Let's say you have three fields - Date, Week and Month. The user selects the month of August 2010, and the week of July 26, 2010 through August 1, 2010. Max(Date) is now August 1, 2010. Max(Date)-1 is July 31, 2010. But that date is disallowed by the Month selection. It therefore isn't good enough to simply override the Date selection. You must ALSO tell it to ignore the Month selection. For similar reasons for different Week and Month selections, you must tell it to ignore the Week.

That doesn't mean you have to ignore every possible date field in your entire data set. For instance, you might have an OrderMonth and a ShipmentMonth. If you're trying to report a selected ShipmentDate along with the previous ShipmentDate, you'll need to tell QlikView to ignore the ShipmentMonth, but you probably do NOT want to tell it to ignore the OrderMonth. Or you might. It all depends on what data YOU want to display.


radam wrote:Also, If I need to manually specify a date format would it be the same format that is set at the beginning of the script?

It would be the date format used by your field, from wherever that field's date format is specified. That can be done at the beginning of the script. It can be done when loading that specific field. It can be done in the document properties.

But if you want to add Date={...} to a set, the specified date inside of the brackets must match the format of the Date field, wherever that was specified. If the Date field is in 'MMM DD, YYYY', then that's exactly what the expression in the brackets has to return. If that's the format specified at the beginning of your script, a mere date(...) should work. If not, date(...,'MMM DD, YYYY') would work.

If you're still having trouble, I suggest making a simple application with some simple data loaded by inline loads that demonstrates the problem you're having.