Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner - Specialist
Partner - Specialist

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

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.

 

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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).

View solution in original post

5 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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).

pnn44794
Partner - Specialist
Partner - Specialist
Author

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],

.

.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.

pnn44794
Partner - Specialist
Partner - Specialist
Author

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!