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
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 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
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
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 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.