Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am aggregating sales value on 3 dimensions in a pivot table(Zone,city,month(horizontal)).How do I calculate vertically on a horizontal dimension. For example, I want to calculate AvgQ1 as avg(Apr-15,May-15,Jun-15) as shown below.
Could you please help me on this? Thanks in advance.
Apr-15 | May-15 | Jun-15 | Avg Q1 | Jul-15 | Aug-15 | Sep-15 | Avg Q2 | ||
North | Delhi | 421 | 410 | 401 | 411 | 432 | 451 | 319 | 401 |
Agra | 1577 | 1523 | 1644 | 1581 | 1886 | 1929 | 1555 | 1790 | |
Jaipur | 180 | 172 | 180 | 177 | 194 | 206 | 209 | 203 | |
Lucknow | 107 | 127 | 149 | 128 | 126 | 137 | 154 | 139 | |
Chandigarh | 272 | 279 | 250 | 267 | 295 | 294 | 282 | 290 | |
Varanasi | 235 | 217 | 237 | 230 | 254 | 278 | 283 | 272 | |
Gurgaon | 187 | 204 | 212 | 201 | 224 | 217 | 244 | 228 | |
North Total | 2980 | 2932 | 3073 | 2995 | 3412 | 3512 | 3046 | 3323 |
I suggested to use only one expression, not creating a second one for the average.
The expression should branch into two evaluations for your details and the partial sum average based on Dimensionality() / SecondaryDimensionality() function.
You just need to enable partial sums for the correct dimensions.
You can create a field that groups your year month values into quarters in the script (e.g. using a master calendar), then use the Quarter field as additional horizontal dimension on top of the year month field.
Use a partial sum on presentation tab on the quarter field and an expression like
=If(SecondaryDimensionality() = 1,
Sum(Sales) / Count(DISTINCT Month),
Sum(Sales)
)
Thank you Stefan for this idea.
After applying this logic , I am getting in the below format. But I wanted to display quarterly average next to month values as shown earlier.
I suggested to use only one expression, not creating a second one for the average.
The expression should branch into two evaluations for your details and the partial sum average based on Dimensionality() / SecondaryDimensionality() function.
You just need to enable partial sums for the correct dimensions.
Thank you Stefan for correcting my mistake. Now its working and I have got as below. Is it possible to not show quarterly dimension row and show partial sum label as 'Avg Q1'.