14 Replies Latest reply: May 10, 2010 7:11 PM by John Witherspoon RSS

    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.





       

        • Re. :Daily Sales
          Martin FAVIER

          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
              John Witherspoon

              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

                  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
                      John Witherspoon

                      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

                           

                           

                           

                           

                           



                           

                          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

                               

                              Ok, I got the formula to work as follows.

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

                               



                               

                              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.

                               

                                • Re. :Daily Sales
                                  juergm

                                  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

                                      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
                                          John Witherspoon

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

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

                                            • Re. :Daily Sales

                                              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
                                                  John Witherspoon

                                                  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.

                                                    • Re. :Daily Sales

                                                      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
                                                          John Witherspoon

                                                           


                                                          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.

                                  • Daily Sales
                                    James Phillips

                                    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