topic Re: Transaction Vs Reporting Dates in App Development

Transaction Vs Reporting Dates

ogster1974 — Thu, 13 Aug 2015 08:54:37 GMT

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 — Thu, 13 Aug 2015 09:35:36 GMT

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

ogster1974 — Thu, 13 Aug 2015 09:53:40 GMT

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?

Re: Transaction Vs Reporting Dates

Gysbert_Wassenaar — Thu, 13 Aug 2015 09:58:13 GMT

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.

Re: Transaction Vs Reporting Dates

ogster1974 — Thu, 13 Aug 2015 10:13:02 GMT

Touche

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

Annie