Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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.
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)
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
Thanks for the recommendation. I started by using a field that is a date called Service Date. Unfortunately, I got exactly the same results.
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.
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.