Replace Above Expression

[#1]

I use above to display the previous data in the custom table. I have a trouble that Above Function doesnt work when I wanna show the Total of Month, Quarter , Week in the following picture:

can anyone help me? please... and another problem, the column 'Prog Year N -1' is % change compare to last year. ( explain : in the row 'July' will show the % change compare to July of last year ).

Thank in advance! 

[petter]

Your sample file does not have any data for last year ...for 2014 at all.

[#3]

oh, I am sorry I send you another sample file !

[petter]

Hi Tuan,

Week is not a "good" dimension to use consistently with the other calendar dimensions. The reason is that week can span two months. If you look at week #5 in 2015 you will see that it appears in both as the last week of January and the first week of February - which is correct - but comparing week #5 of February with week #6 in February does not make sense because they have a very different number of days.

I would advice you to have two variant views  - so I took the liberty of making some dimensions and one expression condtional on a variable where you can select either Year-Week view or Year-Quarter-Month view. See the attached example.

The expression for Year-Quarter-Month is:

Sum(Value) / Above(TOTAL Sum(TOTAL <Product,Year,Quarter,Month> Value),12,1) - 1

The expression for Year-Week is:

Sum(Value) / Above(TOTAL Sum(TOTAL <Product,Year,Week> Value),52,1) - 1

It is almost correct because 52 should sometimes be 53 and it gets even worse you might have a week #1 at the beginning of the year and a week#1 at the end of the same year. Yes - that is dates in the end of the year that belongs to week#1 in the next year. And the way you have made Week#1 with the Week()-function calculates it by the official calendar rules that makes this possible.

So even for Years weeks don't align. But the alignment problem occurs only at the beginning or the end of the year. I haven't tried to solve this for you - and leave it as an extra exercise for you to solve - or you could simply post another question asking for help for getting this alignment issue solved for you.

Hope this helps.

brg

Petter

[#5]

Please call me : Kiet

Thank for your help. I think week dimension is inconvenience when I create a custom table. I will choose difference dimension . Date is good choice, isnt right ?

And there is another problem which you miss.

The row 'TOTAL' wont display the % change and % change compare w Last Year ?

Can you give me a solution for this ? Thank you !

[petter]

Hi Kiet,

Dates aligns with all the other dimensions and also with weeks. To make it easy one can stick to only use combinations of calendar dimensions that align well or have minimal oddities.

I had a look at the problem with the SUB TOTALS and have a new QVW for you.

[petter · Accepted solution]

Sorry I forgot a small change that is fixed now in this version of the QVW...

[#8]

yeah , thank you so much , petter-s‌ !  its helpful

[#9]

Hi petter-s‌,

I am sorry about this. I add your expression :

Pick( Dimensionality()

, Column(1) / Column(2) - 1

, Column(1) / Column(2) - 1

, Column(1) / Column(2) - 1

,Num((Sum(NetSale)/ Above(TOTAL Sum(NetSale)) - 1 )*100,'#.#')&'%')

to calculate the row 'TOTAL' but it doesnt work.

Can you check my attached file that why it does work,pleassee! !

[petter]

Actually what looks like columns visually aren't columns in your chart. If you add a second expression and put in this:

Column(1)

You will see that your first expression is really your Column(1) ... so even though the pivot table creates visually extra columns they are not actual columns counting as such when it comes to the Column()-function. Quite confusing yes.

You will have to repeat your part-expressions which you already have in your rather complex expression to be able to use these values...