Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Daily Sales

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.





14 Replies
Not applicable
Author

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!

johnw
Champion III
Champion III

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.

james
Creator III
Creator III

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



Not applicable
Author

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?

johnw
Champion III
Champion III


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.