Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted

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
Highlighted

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

Highlighted
Partner
Partner

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

.

.

Highlighted

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.

Highlighted

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.

Highlighted
Partner
Partner

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!