topic Re: How to suppress zeros in QlikView

How to suppress zeros

posywang — Thu, 16 Mar 2023 17:58:58 GMT

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.

Re: How to suppress zeros

marcus_sommer — Mon, 20 Mar 2023 14:15:28 GMT

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.

Re: How to suppress zeros

posywang — Mon, 20 Mar 2023 15:09:49 GMT

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

Re: How to suppress zeros

marcus_sommer — Mon, 20 Mar 2023 16:02:54 GMT

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.

Re: How to suppress zeros

posywang — Mon, 20 Mar 2023 16:20:53 GMT

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.

Re: How to suppress zeros

marcus_sommer — Mon, 20 Mar 2023 16:35:13 GMT

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

Re: How to suppress zeros

posywang — Wed, 22 Mar 2023 15:31:59 GMT

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.