Transaction Vs Reporting Dates

[ogster1974]

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.

[Gysbert_Wassenaar · Accepted solution]

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])

[ogster1974]

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?

[Gysbert_Wassenaar]

You're welcome Annie.

A pick-match combination will perform better than deeply nested if's. With if statements every branch is always evaluated. You won't see any difference with only a few simple nested if statements, but with a lot of nested if's performance can bog down. A pick-match could improve things in such situations.

[ogster1974]

Touche

I do have a localization If statement already so getting that way.  Might give this approach a whirl.  Thanks for your time.

Annie