How to suppress zeros

[posywang]

Hi,

We are reading from two files, one is last 12 months, and another is current month. I was asked to put them into one pivot table with a last 12 month subtotal before current month column. I was able to add a Flag to indicate which months are last 12 month and which month is current month. However, I got bunch of zeros that i could not get rid of. Can anyone help me on that? Please see attached for a test file I created. 

[marcus_sommer]

If your flag is a valid field and parallel to the period-information you will have the mentioned dimension-structure - means no overlapping of periods between the sources.

[posywang]

Thank you for your response! Can you please make it more specific? This issue is holding up my work. Much appreciated!

[marcus_sommer]

By a data-structure like:

t: load * inline [
Period, Source
2022-02, Last12Months
2022-03, Last12Months
...
2022-03, CurrentMonth
];

you would have both information appropriate related to each other and could create the pivot without an overlapping and applying the needed partial sums.

It should be easy to get just by concatenating both files and adding the source-information respectively the flag.

[posywang]

Your data structure is the same as my current data structure. You can take a look at the qvw that I posted. The column with zeros don't go way no matter what I do. 

[marcus_sommer]

Just use the flag as horizontal dimension with a single expression.

[posywang · Accepted solution]

Ok, after trying many different ways, my final solution is to add sequence number to each month and then create separate expressions for 12 months each (set analysis with the sequence number1-12), the subtotal expression for the last 12 month (set analysis with all sequence numbers less or equal to 12), and then the expression for current month (set analysis with sequence number 13). Single expression will create zeros unfortunately.