Pivot to show months, total (of selected months) and ytd from April this year

[simon_ball]

Hello.

I've done a couple of years of QV back end scripting but i am now moving into expressions in front end, enhancing the suite of reports.

the Finance guys like their pivot which shows columns for months, and they want the total to remain showing total for selected months, but they also want a new column adding to show sum(months(april this year to last month))

I assume one of you experts can whip me up an expression so i can pick it apart i can probably work that out in a couple of hours..., but the bit i have no idea how to add this to the current column which is based on the fiscal month dimension...where does the total get defined, and can a pivot in qv 11.2 have 2 total columns...?

thanks in advnace

[Gysbert_Wassenaar · Accepted solution]

A pivot table can't have two total columns. But perhaps you can add a table in the script with a new field for reporting.

Reporting:

LOAD * INLINE [

ReportDim, Month

Jan, Jan

Feb, Feb

...etc

Dec, Dec

YTD, Apr

YTD, May

..etc

YTD, Nov

Total, Jan

Total, Feb

...etc

Total, Dec

];

The ReportDim values will be linked with the all relevant Month values. You can use this new field in the pivot table and get the reporting values (months, YTD and total) and the totals that go with each value.