Chart / Table with dynamic time dimension (mixed time dimensions)

[konHi]

Hi,

I have the following (very special) customer question.

The customer wants to have a table, where he can see the last 3 years / last 12 months / last 5 weeks starting from a selected date. (e.g. 16^th Oct.2015)

I managed to meet his requirements by making a pivot chart and adding 3 expression for the years, 12 expressions for the months, 5 expressions for the weeks. Every expression has a set analysis where I can slice the right time range. Every expression has also an if statement, it checks what value dim 2 has to pick the right calculation (sum / or avg…) method. Now we have a much more data in our application and the calculation takes now more than 1 Minute => calc Time out. (we have more than 20 expressions and many if statements)

I made a test and created 3 pivot tables (year / months / weeks) and made 5 expressions (N / G / R / Min/ Stück), now the calculation is very fast (under 10 seconds).

=if(ColumnNo(total)<=3,[CalYear],CalMonth)

My question is how can I put all 3 in one pivot?

My idea is:

Make a dynamic time dimension ( Y , Y , Y , M , M ,M …W, W,…)

I tried it by using if statements an Rowno() / columnno() but failed. I know that the dimension doesn't know the columnno at this point.

Does anyone have a tip or a new idea how to solve the issues?

Thank you very much

[konHi]

please guys i am getting crazy with this problem

[ElizaF · Accepted solution]

Hi,

Try to use "The As-Of Table" concept. The idea is to create a secondary table with rolling dates. Will be a huge table, but  I thing will resolve your question.

In below links you can find more information.

The As-Of Table

Calculating rolling n-period totals, averages or other aggregations

Combining AsOfDate with "normal" date

Hope this helps.

[konHi]

Thank you ElizaFilip_2014. AsOfTable was the right idea. Thank you very much!!!

[ElizaF]

Welcome. I am glad it did help.

In terms of qvw file performance, which is the result?