topic Re: How do you combine a dimension with the same value for a total sum? in QlikView

How do you combine a dimension with the same value for a total sum?

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

Hello:

I have a dimension, Approved_YearMonth, where I have multiple values that are the same, i.e. 201701 (5 times), 201702 (12 times) and 201703 10 times (will eventually have all of 2017 as the months go by). What I'm trying to accomplish is just three columns, meaning one 201701 that is a sum total of the 5 that exist, one for 201702 combining the 12 and one for 201703 combining the 10. Additionally, there is 2016MM data (where MM is the two digit month value) that I do not want to include in the chart. I am able, using the following expression, to keep the 2016 data out of the sum.

Sum({<Approved_YearMonth={"*"} - {'2016*'} >}SAN)

How can I accomplish this? As always, thanks in advance for any and all help.

Re: How do you combine a dimension with the same value for a total sum?

Peter_Cammaert — Tue, 28 Mar 2017 14:17:20 GMT

Although you think that your dimension contains just values for YearMonth, that's actually just the formatting. The underlying numerical value probably includes the Day as well, which causes multiple seemingly identical dimension values to be displayed.

That's how QlikView works.

You can solve this by converting your chart dimension into a Dimension expression like:

=Date(MonthStart(Approved_YearMonth), 'YYYYMM')

How to do this? Open the Chart Properties->Dimensions tab, select the current dimension from the tree and click the Edit... button (far right).

Re: How do you combine a dimension with the same value for a total sum?

pnn44794 — Tue, 28 Mar 2017 14:40:14 GMT

Hello Peter:

Thanks for the response. I thought I had taken care of that potential problem with this line of code in my Load Script. I'll try your suggestion and let you know.

LOAD
.

Date(Date#([Approval Date], 'M/D/YYYY'), 'YYYYMM') as [Approved_YearMonth],

Re: How do you combine a dimension with the same value for a total sum?

Peter_Cammaert — Tue, 28 Mar 2017 15:07:30 GMT

No, the Date() function will only adjust the formatting (the text part of the dual value), not the underlying numerical part. The Days are still there.

You can easily check this out yourself by creating a straight table with dimensions Approved_YearMonth and =num(Approved_YearMonth) and your bar chart sum(...) as expression.

Re: How do you combine a dimension with the same value for a total sum?

Peter_Cammaert — Tue, 28 Mar 2017 15:14:16 GMT

BTW if you want to know what the heck these numerical values represent, think about the integer part representing the day number since Dec 30, 1899. The fraction represents the time as a part of one day (meaning that 0.25 equals 6 o'clock in the morning of day 0)

If you use Excel, you will recognize this encoding. They're just the same in QV and in Excel.

Re: How do you combine a dimension with the same value for a total sum?

pnn44794 — Tue, 28 Mar 2017 15:17:20 GMT

Ah ha! So I've definitely learned something here. I thought I had indeed eliminated the day piece of the date but all I did was just reformat the date minus the day. Thank you for the explanation.

I implemented your suggested fix and it worked. Thank you very much!