4 Replies Latest reply: Aug 13, 2015 6:13 AM by Andy Weir RSS

    Transaction Vs Reporting Dates

    Andy Weir

      Hi Guys

       

      I'm trying to get a solution to work where I offer the users the choice of viewing their data from 2 separate time point of views.  The measures stay the same.

       

      Transactional:  The date the action took place (hangs off my fact table)

      Reporting: The date the action should be reported in (hangs off my dimension tables)

       

      I have separate calendars to handle the different views wired into a master calendar.  The issue comes when I want to use date as a dimension.  If I use Reporting Calendar or Transaction Calender as the dimension it works fine but what I want to do is flip this based on a user selection.

       

      Example

       

      I've tried using the Master Calender as a dimension and making the measure link to it but its not working.

      Dimension

      [%YYYYMM]

      Measure

      If(vReportingPeriod='T',

           COUNT(DISTINCT{<ActionType={'CLAIMCLAIMED'}, [%TrnYYYYMM]=p([%YYYYMM])>} [%ClaimKey])

      ,

           COUNT(DISTINCT{<ActionType={'CLAIMCLAIMED'}, [%ClaimYYYYMM]=p([%YYYYMM])>} [%ClaimKey])

      )

       

      If I use as Dimension

      [%TrnYYYYMM]

      [%ClaimYYYYMM]

      They work

       

      In QlikView I would probably create separate charts and overlay them allowing the show/hide on the setting of the variable but I cannot find such a solution of Sense.

       

      Any Ideas? I guess i'm looking for a way to make my dimension setting dynamic or a way to hard wire the measure to a data island.

        • Re: Transaction Vs Reporting Dates
          Gysbert Wassenaar
          If I use Reporting Calendar or Transaction Calender as the dimension it works fine but what I want to do is flip this based on a user selection.

          In essence you want to let the user choose which calendar to use?

           

          Ok, there's an extension called qsVariable avaible. You can use that to create a variable and a set of values for the variable. You can display these values as buttons. And use the variable in a calculated dimension in your chart. Something like =pick(match(vCalendar, 'Transaction', 'Claims'), [%TrnYYYYMM],[%ClaimYYYYMM])

          • Re: Transaction Vs Reporting Dates
            Andy Weir

            Thanks Gus. I'm already using that to set variables in my dashboard.  Working nicely to set the vReportingPeriod. 

             

            I can swap the measure based on Transaction or claims and works a treat for bar guages but my charts use the calendar as a dimension.

             

            I see where my issue is now.  I'm building my calendar dimension as a Master Item as I made it a drill down from quarter to month.

             

            If you use Master items in this way you cannot reference in an expressions yet.  I guess I have to decide which is the best feature to have drill down to month from quarter or being able to switch reporting views.

             

            As a follow on question would you us pick(match..... of If(..... they seem to do the same thing.  Is it a question performance as to which method to use?