Dates In Qlik Sense while using functions causes Total Sum

[brentski]

Thank you all for the great community support.  I have learned a lot in a short time through searching for answers.  But I am stuck.

I am Trying to Create a Graph that shows Q1, Q2, Q3, and Q4 for 2018, 2019 which then shows the total number of Member Months for those Quarters by Year

I have a Field called Period which includes the Values 2018Q1, 2018Q2,...2019Q3.  When I make a table and put Period and Member Months I get the appropriate values by Period. But when I edit the Period I get total and they repeat for every Period.

Period           Member Months

2018Q1                 5

2018Q2                 7

2018Q3                 6

2018Q4                 5

2019Q1                  9

2019Q2                 8

When I change Period to be =right(Period, 2) and also create =left(Period, 4) I get:

Year    Quarter     Member Months

2018      Q1              40

2018       Q2             40

2018       Q3              40......

How do I get 2018 Q1 to show 5?

If I add Period back into the table everything works as expected (except I can't graph that way).

[Arthur_Fong]

Do you have a date column in your table?

If yes, try building Year and Quarter using date column.

=Year(date)

='Q' & ceil(month(date)/3)

[Gopi_E]

Hi,

do like this left(period,4) as year

then by using that year field 

Date(year,'YYYY') as year_New

then if you want quarter use year_New  field

then using quarter function you can create Quarter respected year

[brentski]

Thanks for the recommendation.  I started by using a field that is a date called Service Date.  Unfortunately, I got exactly the same results.

[brentski]

I continue to try and troubleshoot.  If I do anything to the field called period it sums all of the member months.  I simply did =text(Period) and the data returned is for all of the time periods selected.  Why does applying a function to the field cause the field to behave this way?

Please Note that I do not have access to the data model only to the front end.  So creating a Master Calendar table or adding another table are not options for me for this solution.

[brentski · Accepted solution]

So, I have a solution but not the answer.  I do not understand why doing a function to a dimension causes it to loses its level of aggregation.  However here is my solution:

Dimension

=Date#(Left(Period,4))  -->This gives me the year portion of Period

=Date#(Right(Period,2))-->This gives me the Quarter portion

Measure

=aggr(sum([Member Months]), Period)

This solution allows me to have 2 or more years on a single graph where Quarter is along the X axis Member Months along the Y axis and Years are the different lines.