6 Replies Latest reply: Oct 7, 2013 2:50 AM by Chanel Choo RSS

    Chart data populated in 2 date ranges

      Dear Community,

       

      I need help for a complicated request to create 2 date range (analysis period and comparison period) and compare the data in 2 time periods on charts.

      Attached herewith the .qvw file.

      I have created 2 date ranges with variable (vStartDate, vEndDate, vComparisonStartDate, vComparisonEndDate), which is working fine!

      But the problem is the expression in the combo chart is not working. Appreciate if someone can help.

       

      The complex requirement:

      Bar Chart shows analysis period data

      Dot Chart shows comparison period data

        • Re: Chart data populated in 2 date ranges
          Stefan Wühl

          Chanel Choo,

           

          there are at least 2 issues with your app:

           

          First, you are using these expressions in your chart

          =Count({<ComparisonShortCreatedDate = {'>=$(vComparisonStartDate)<=$(vComparisonEndDate)'}>} Post)

          =Count({<ShortCreatedDate = {'>=$(vStartDate)<=$(vEndDate)'}>} Post)

           

          But ComparisonShortCreatedDate and ShortCreatedDate are fields in two data island tables, these tables are not connected to your fact table, so selecting in these fields won't affect your Post coúnt (that's why you see the total count for both expressions).

           

          To fix this, you need to make a selection in a date fields that's linked to your fact table. But there is a second issue:

           

          Your fact tables key field CreatedDateTime is a timestamp. The way you created your master calendar, you won't get a working link to your fact table, because you created key values in your master calendar like

           

          Temp:

          LOAD Min([CreatedDateTime]) as MinCreatedDateTime,

               Max([CreatedDateTime]) as MaxCreatedDateTime

          Resident Temp1;

           

          DROP Table Temp1;

           

          LET varMinCreatedDateTime = Num(peek('MinCreatedDateTime',0,'Temp'));   

          LET varMaxCreatedDateTime = Num(peek('MaxCreatedDateTime',0,'Temp'));

          LET vToday = Num(Today());

           

          //*************** Temporary Calendar ***************

          TempCalendar:

          LOAD

                 $(varMinCreatedDateTime)+IterNo()-1 AS Num,

                 Date($(varMinCreatedDateTime)+IterNo()-1) AS TempCreatedDateTime

              AUTOGENERATE 1 WHILE $(varMinCreatedDateTime)+IterNo()-1<= $(varMaxCreatedDateTime);


          This will create a record per day starting from your min fact timestamp, adding always 24 h.


          7/30/2013 1:42:00 AM

          7/30/2013 1:42:00 AM

          7/30/2013 1:43:00 AM

          7/31/2013 1:42:00 AM

          8/1/2013 1:42:00 AM

          8/2/2013 1:42:00 AM

          8/3/2013 1:42:00 AM

          8/4/2013 1:42:00 AM

          8/5/2013 1:42:00 AM

          8/6/2013 1:42:00 AM

           

          But you are missing links to fact key values:

           

          8/6/2013 4:02:00 PM

          8/6/2013 4:03:00 PM

          8/6/2013 10:35:00 PM

           

          [you only get working links between you tables, if the key values numeric values are identical, so even when it looks the same for the text representation, there might be further issues when matching the floating point values]

           

          I think you should create a date field from your timestamp already in your fact table:

           

               Daystart(CreatedDateTime) as CreatedDateKey,

           

          and use this to build up your master calendar.

           

          Then, in your expressions, use something like

          =Count({<CreatedDateKey = {'>=$(vComparisonStartDate)<=$(vComparisonEndDate)'}>} Post)
          =Count({<CreatedDateKey = {'>=$(vStartDate)<=$(vEndDate)'}>} Post)


          You may need to take care of your date field formats (so it might be good to use numeric fields for these kind of selections).

           

          This should make your app work, if not, please post your new version (and it's helpful if your app can be reoloaded, so if possible, use an INLINE LOAD to include some sample data records instead of a DB connection).

           

          As an alternative approach, you can also look into alternate states, available since QV11, to do comparative analysis (e.g. have a look into the What's new in QV11 sample app).

           

          Regards,

          Stefan