6 Replies Latest reply: May 9, 2011 4:41 AM by Ian Kingon RSS

    Calculated Dimensions

      Hi All,

      If I have read in a database table which essentially has an hourly snapshot of a customers stockholding at various sites each day. How would I create a table that has the customer name and a peak stockholding (i.e. the highest hourly amount) and a latest stockholdong (i.e. the last hourly snapshot taken).

      So as an example, I exported the data from the QV worksheet and if you autofilter for customer 455 on the 2011-01-02 for area B6, the following hourly (actually 4 hourly) cubic meter holdings were rrecorded:

       

      2418.687351
      2418.687351
      2551.833743
      2440.649759
      2440.649759


      So I want to be able to find the highest number for the peak hourly (2551cbm) and the latest (2440cbm) for the last snapshot taken.

      Could you suggest the best way of calculating these two value for customer 455, area B6 and date = 2 Jan? Would I be able to sum the peak amounts at different locations for the same customer on the same date?

      Cheers
      Ian

        • Calculated Dimensions

          Sorry, the Excel attachment would have helped as well.

          • SV:Calculated Dimensions
            Toni Kautto

             

            You should be able to find the higest value by using Max() on the ValueField. The latest record should also be possible to find by using Max() on the timestmap of when your record was added. If you run those as expressions on the customer as dimension you should get a chart object with the customer highest an latest values plotted.



              • Re: Calculated Dimensions
                Toni Kautto

                =sum( {$<[Storage_Details Actual PROCESSED_DATE]={

                "=TimeStamp( Aggr(Max([Storage_Details Actual PROCESSED_DATE]),[Storage_Details PROCESSED_DATE], [Storage_Details PRIN_CODE], [Storage_DetailsSITE_CODE]) )"
                }>}
                [Storage_Details VOLUME])

                 

                  • Re: Calculated Dimensions

                    Hi Toni,

                     

                    Aaaaargh, at least the forum is back up . . . I added the formula, but it seems to calculate not the last, but the volumes associated with the first snapshot of the day.  I tried with single and double quotes - no change.  I tried the timestamp() expression by itself and it certainly calculates the expected timestamp, but when inserted into the rest of the expression, the results do not match what I would expect to see.

                     

                    Also an example is for principal 455 on the 3-Jan-11, when the data set is constrained to this principal and this date, the result for the latest volumes are all zeroes?

                     

                    I have also started a new post with a more descriptive heading.  Any additional ideas?

                     

                    Regards

                    Ian

                      • Re: Calculated Dimensions
                        Toni Kautto

                        Only thing I saw was that you rapped the first dimension with Date() and Floor(), which is not recommended as it will only floor the presented value and not the actual data in the table. It is better to that kind of operations in the load script, soi that you have control over the datat you use in calculations.

                         

                        If you alter your listbox with dates to an expression, you can see that your dates are not floored so it might be a good idea to do so in the script; =num([Storage_Details Actual PROCESSED_DATE])

                          • Re: Calculated Dimensions

                            Hi Toni,

                             

                            Okay, I got you on that one - I should have used [Storage_Details PROCESSED_DATE] which is already FLOOR()'ed.  But even if I change that, the expression does not work as logically one would expect.

                             

                            The problems I can see are as follows:

                             

                            1.  It does not select the last timestamp'ed entry and use that volume, it seems to use the first entry.

                             

                            2.  If there are fewer locations in the last snapshot taken, than calculated by the max expression, all values are zero for the set analysis.

                             

                            3.  I was reviewing with my manager, and the actual result that they want is the last snapshot - and not the latest snapshot per site.  So if the last snapshot only showed two sites as having volume, a third site that has a volume based on the daily maximum, should actually read zero as there was nothing in that site at the end of the day.

                             

                            I am very eager to solve this and move along with the development of the dashboard, but this is slowing me down and I am so frustrated.

                             

                            You could post any additional suggestions to:

                             

                            http://community.qlik.com/message/114407#114407

                             

                            Which is the new post I created using the simplified spreadsheet to deal with just this issue I need to solve.