Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I need to create a pivot table report in the following format:
Product code | Customer | Dates | Wk1 | Wk2 | Wk3 | Wk4 | Wk5 | Wk6 |
KG/CASE | depends on < | etc | etc | etc | etc | etc | ||
NSV/KG | 100 | etc | etc | etc | etc | etc | ||
COS/KG | 80 | etc | etc | etc | etc | etc | ||
Profit/KG | 25 | etc | etc | etc | etc | etc |
The Expressions for KG is "_exp_KG", however there is no dimension that provides the required results (Smililarly "_exp_CASE" for Cases)
If possible I would also like to make the expressions ie NSV/KG rely on the cyclic group(/chosen option).
Do you know how I can achieve this without publishing two reports?
Thanks, Gareth
Gareth,
I don't quite understand why "there is no dimension that provides the required results" so I don't understand your problem completely, but to answer your direct question you can make an expression depend on the dimension selected in a cyclic group by using the function getcurrentfield(Group_Name).
You can also create a cyclic Expression group using the Group and UnGroup buttons in the Expressions tab.
Regards.
Karl Do you have an example of ""You can also create a cyclic Expression group using the Group and UnGroup buttons in the Expressions tab"
If you select an expression in the editor and hit the group button it will group it and the expression above it in a cyclical expression. You can then cycle through multiple expressions in a chart, much like a cyclical group used in a dimension.
I grouped a couple expressions but I did not see a method to Cycle through them after I created the group. I can only see the value for the first expression in the group.
Where should the button to cycle thru the expression appear?
In a table it will appear in the column header, in a chart it should appear in one of the corners. Think you need to have 'expression as legend' selected as well.
Thank you Leonard
It appears to not be available for Pivot Tables, the expression as legend does not exist, probably only for charts
You can certainly do it within a pivot table, just group them, the
cyclical will appear in the column header. The 'expression as legend' option
does indeed only exist for graphical type charts.
Something to maybe consider... Setup a cyclical group for your dimension,
then in your expressions do an if statement similar to:
if(getcurrentfield() = 'A' then sum(sales)
if(getcurrentfield() = 'B' then sum(hours)))
Will allow you to 'customize' your expressions based on your dimension (or
any other field for that matter, doesn't need to be related in any way - you
could setup an inline load and then point the expressions at it)