Data Model:Data pivot up for time dimension

[#1]

HI QV community,

Background

This question is around QlikView data modelling. We have a number of dimensions(Eg. Geography, Time etc) and one standard metric/fact - Sales.

On our dashboard we have flexibility to look at calculated metrics (based on Sales) and based on changing dimension values.

E.g. - Sales for a geography in user defined(via selections) time period

Challenge

Can we remove the time dimension and store the sales in pivoted up fashion for different values in time dimension (Eg. Weeks). This is a client requirement where the client wants to pivot up data(reduce row count) to enable quick data processing(over small volume).

We need to know if we can have the weekly data(time dimension) pivoted up and our QV dashboard can still function properly.

While we are interested to know the feasibility, we are also keen to understand if this will impact performance (due to change in expressions). Apart from simple metrics like sales, share in our ad-hoc report, we also ave metrics like contribution over dynamic dimensions.

Our Understanding

While we do have range-sum function in Qlikview which can add values across columns, can this calculation be dynamic(columns chosen by user interaction on dashboard) . Eg if the user chooses May 2014, we see the sales for May 2014(aggregated over weeks lets say) only.

Illustration

____________Now___________

Geography	Time	Sales
Sample Geog	Week 1	10
Sample Geog	Week 2	20
Sample Geog	Week 3	30
Sample Geog	Week 4	40

_________________________________________________Proposed_____________________________________________________

Geography	Week 1	Week 2	Week 3	Week 4
Sample Geog	10	20	30	40

Please let us know your thoughts,risks and challenges around the above implementation.

[Gysbert_Wassenaar · Accepted solution]

If you go with your proposed data model you will have a lot more work getting your expressions to work. Aggregating over different fields is a lot harder than aggregating over one field. I recommend against it. Building dynamic rangesum expressions is not for the faint at heart. And such expressions are likely to perform quite badly.

[#3]

Hi Gysbert,

That was spot-on. Thanks a lot. I am sure we have saved a lot of time and effort based on your inputs.

Background

We have a dashboard based on ad-hoc report. This dashboard allows us to see the different metrics such as product, sales and market sales for various dimension values/data points over time (weekly data for 2 years). E.g. We can see Product sales for all territories.

Technical Details - Chart Type - Line Chart, Dimensions are changed using the visibility condition.

We also have fast charts to change the display from Line chart to straight table

Geography Hierarchy - Territory<District<Region<Area

Challenge

When we look at smaller data sets, such as all territory within district we can see the trend lines for all the territory.

However, if we show all territory within an area the chart takes a lot of time to refresh. The straight table appears quickly but the trend lines take significant time.

We need to limit the number of trend lines being displayed on the charts. Also, will it be possible to show the user a warning message, if the user tries to see too many trend lines (say >100) on the chart.

Our Understanding/Approach

We can create a variable (E.g.- vShow) to identify the selected dimension and calculate the distinct number of records for this dimension. The chart calculation condition will be dependent if the vShow value is less than 100. Also we can create a text box (for warning message) which will be displayed when the vShow value greater than 100

Queries

1. Can you confirm, if the above approach is the right one? Is there a better way to implement it(for better performance)?
2. Can the chart type be changed automatically to straight table (using fast chart) when we have the number of trend lines exceeding threshold value >100 (based on variable vShow)?
3. Alternatively, can the user have an option to stop the calculation of the charts and go back to previous selections, i.e. default state. We have a clear filters button. However the clear filters button is not clickable, when the chart is being calculated.

PS: We cannot share the dashboards

Thanks

Kaushik Ranjan

Data Model:Data pivot up for time dimension

[Gysbert_Wassenaar]

1. Using the calculation condition is a good idea.

2. Not as far as I know. It's easier to use two chart objects and show/hide them as needed.

3. No, a selection will immediately cause a recalculation of the charts. There's no way to prevent this.

[#5]

Hi Gysbert,

Thank you so much !! .