Dynamic captions
Hi all,
I have three calculated columns in a table that I would like to name dynamically. Each of the column corresponds to a specific month of the quarter. The calculated fields are derived using data from a specific quarter.
Using the dynamic captions editor I have the option to select month from our calendar dimension. However, I only have the options choose between first/last/all. First gives January, last March, how do I get February for the second column?
Any alternative suggestions on how to do this, short of manually typing the names?
Regards,
Kenneth
1
Comments
Hi Kenneth
I gave it a try :-)
Here's a setup similar to your - except I added an extra element to the user dimension:

Now I add the calculations as measures (not calculated columns). This will initially look chaotic because there will be calculation in every month - like shown below:

Now working with properties, visibility I will hide a range of these calculations:
Month1 (from 1 from the first - and click Define range end);

to 6 from the first and click apply:

One more range needs to be hidden - so click hide a range again - this time from 8 to 9 from the first
When you're done you should only have the 7th occurence of the caluculation left - it's shown like this in the dialogye:

2nd calculation should be hidden like this:

And last one like this:

That leaves me with this crosstab:

Now I just have to hide my initial measure with this setup:
With a final touch by setting up dynamic captions as space for the calculations - my final crosstab will look like this:

You should of course name the 3rd element of the user dimension better (and also the calculations) - but with a lot of hiding - the calculations are now shown under the months where they belong.
Hope it makes sense
br/Niels
If it is similar to your screenshot, you can try something like:
if allcount(d1,d1:0,m1)= 1 then a else
if allcount(d1,d1:0,m1)= 2 then b else
if allcount(d1,d1:0,m1)= 3 then c else
if allcount(d1,d1:0,m1)= 4 then d else
if allcount(d1,d1:0,m1)= 5 then e else
if allcount(d1,d1:0,m1)= 6 then f else
if allcount(d1,d1:0,m1)= 7 then g else
if allcount(d1,d1:0,m1)= 8 then h else
if allcount(d1,d1:0,m1)= 9 then i else
if allcount(d1,d1:0,m1)= 10 then j else
if allcount(d1,d1:0,m1)= 11 then k else l
Of course replace the letters with your expressions.
For this to be a robust solution - you must be sure that there will always be 12 rows in that particular sequence.
Will that work for you?
Hi Kenneth
Could you perhaps thrown in a screenshot (with hidden numbers) to explain exactly what you want to do?
I think there could be a solution involving a user dimension - but I would need to understand the details a little better to help out.
br/Niels
Hi Niels
Hopefully the screenshot makes sense.
It is the "måned 1, 2 & 3" values im trying to replace with actual month values. The remaining naming, "2020 - 4. kvartal" is already done using dynamic captions.
BR/Kenneth
Hi Niels
It makes sense :)
Your solution works just not for my specific case, at least not yet.
The reason why I went with calculated fields instead of a calculated member, is that I haven't quite found a way to make a calculated member do what I need it to do.
I essence this is what I am trying to build (need to):
As far as I can tell, I cannot make a calculated member differentiate the calculation based of value in UD2 or by line. Is that possible? in that case your solution would work perfectly.
BR/Kenneth
Hi Niels
Based on some initial testing, it should. The report has 60 or so lines. Writing that nested if is going to take ages. I hope there is not a limit on the length of a calculation expression...
Thank you for your brilliant suggestions :-)
BR/Kenneth
ok - one improvement is possible - if (like in your screenshot) there are 6 calculations repeated, you can use the mod function to repeat the same behaviour every 6th time.
mod(allcount(d1,d1:0,m1);6)
The allcount part counts the rows 1.2.3..... - however because the allcount is used in a mod, every result is divide by 6 and only the remainder is kept.
1/6 - result 0 remainder 1
2/6 - result 0 remainder 2
3/6 - result 0 remainder 3
4/6 - result 0 remainder 4
5/6 - result 0 remainder 5
6/6 - result 1 remainder 0
7/6 - result 1 remainder 1
8/6 - result 1 remainder 2
9/6 - result 1 remainder 3
10/6 - result 1 remainder 4
11/6 - result 1 remainder 5
12/6 - result 2 remainder 0
So by using the mod in your if sentence, you can limit the outcomes to 6.
if mod(allcount(d1,d1:0,m1);6)=1 then a else
if mod(allcount(d1,d1:0,m1);6)=2 then b else
if mod(allcount(d1,d1:0,m1);6)=3 then c else
if mod(allcount(d1,d1:0,m1);6)=4 then d else
if mod(allcount(d1,d1:0,m1);6)=5 then e else f
Please sign in to leave a comment.