Aggregating Quarters

[ShellyG]

Hello guys, 

I have an issue with a calculation with a specific visualization...

I have a quarter field, that is in the following format - 2018Q1, ..., 2018Q4, 2019Q1, etc. It is not possible to have a calendar in the script, as I do not have a date field in my data, so I use directly the quarter field. 

Basically, I have created a bar chart and I have used the below calculation in order for each bar to be showing a specific Quarter and to show the data that I need: 

Sum(Data)/Aggr(NODISTINCT Sum({$}TOTAL <QuarterField> Data), QuarterField)

This is resulting in the bar chart to be stacked up to а percentage and it is working the way I want it to work. 

Now, I want to create a similar logic for a visual, but I want it to be a line chart, and the y axis should be displaying the actual value (not in percentage) of the Data field that I have and the x axis should be distributed to the different quarters that we have so I tried something like:

Aggr(NODISTINCT Sum({$}TOTAL <QuarterField> Data), QuarterField)

and 

Aggr(NODISTINCT Sum(Data), QuarterField)

But they don't work... Any ideas on how to edit the calculation so that it can show the actual values on the y axis, the quarters on the x axis and the line to be representing the data for the dimension distributed to the quarters?

Thanks in advance!

[JordyWegman]

Hi Shelly,

The AGGR is giving you an array of values and that cannot be displayed in 1 field (one line point). Try this:

Sum(Aggr(NODISTINCT Sum({$}TOTAL <QuarterField> Data), QuarterField))

Jordy

Climber

[ShellyG]

Hi Jordy,

Thank you for your suggestion! 

I have added two dimensions - as a group I chose "QuarterField" and as a line I chose "CustomerName" field.

Then I used the measure that you suggested - Sum(Aggr(NODISTINCT Sum({$}TOTAL <QuarterField> Data), QuarterField))

But the chart remained empty (no lines are appearing)...

Cheers,

Shelly 

[ShellyG]

Hello guys,

I figured it out so in case someone is searching for a similar solution, I will post mine here as it might be helpful to someone in the future:

Firstly, I changed my formula to a simple sum, so now it looks something like this:

Sum($(vVariable)) as I have a switch and I need the formula to be working with the options the switch gives. 

Another thing that I did was to change the number formatting to custom with the following format #,##0.

Have a wonderful day everyone!

Cheer,

Shelly