Comparing with previous period's data in a bar chart without the period itself in field wells

Question & Answer
, ,
1

Hi,

Is there a way to compare with last period’s data in a visual WITHOUT using the period as the X-axis? I am trying to have two sets of data - one for the current period and the other for the previous period - side by side in the bar chart below, but since the date field used in the periodOverPeriodLastValue() function is not included in the field wells, I could not bring the previous period data in the visual.

Would appreciate any insights! I tried to look up answers relevant to previous period comparisons but all of them seemed to have the period itself in the field wells.

image
image721×476 13.9 KB

1 Like
2

Hello @no_hair, we should be able to sort something out for this depending on your calculated fields. For instance, I made something like this work by creating a few different calculated fields. First, we will need a X-Axis value. Something that I have found super useful for creating groups is to create some kind of arbitrary denseRank calculation that will then be used in an ifelse statement to return the group names you would want to see.

Rank Field = 
denseRank(
  [{User ID} DESC],
  [],
  PRE_AGG 
)

Then using maxOver and minOver with some division, we can create the categories for the visual:
X-Axis =

ifelse(
{Rank Field} >= minOver({Rank Field}, [], PRE_AGG) AND {Rank Field} < maxOver({Rank Field}, [], PRE_AGG)/4, 'Category 1',
{Rank Field} >= maxOver({Rank Field}, [], PRE_AGG)/4 AND {Rank Field} < maxOver({Rank Field}, [], PRE_AGG)/2, 'Category 2',
{Rank Field} >= maxOver({Rank Field}, [], PRE_AGG)/2 AND {Rank Field} < (maxOver({Rank Field}, [], PRE_AGG) - maxOver({Rank Field}, [], PRE_AGG)/4), 'Category 3',
{Rank Field} > (maxOver({Rank Field}, [], PRE_AGG) - maxOver({Rank Field}, [], PRE_AGG)/4) AND {Rank Field} <= maxOver({Rank Field}, [], PRE_AGG), 'Category 4',
NULL
)

Now, you can create the calculations to receive your values and link them to the category groupings you want. I am using now() to create groups for last year to this current date (June 20) and YTD. Basically, you would want to filter within the sumOver functions rather than filtering the visual directly:

Y-Axis =

ifelse(
{X-Axis} = 'Category 1',
sumOver(
    ifelse(
        {Order Date} >= 
            addDateTime(1-extract('DD', now()), 'DD', 
                addDateTime(1-extract('MM', now()), 'MM', now())
            ) AND {Order Date} <= now(),
        Sales,
        0
), [], PRE_AGG),
{X-Axis} = 'Category 2', 
sumOver(
    ifelse(
        {Order Date} >= 
            addDateTime(1-extract('DD', now()), 'DD', 
                addDateTime(1-extract('MM', now()), 'MM', 
                    addDateTime(-1, 'YYYY', now())    
                )
            ) AND {Order Date} <= addDateTime(-1, 'YYYY', now()),
        Sales,
        0
), [], PRE_AGG),
NULL
)

This is a little long winded, but doing this process allowed me to create the bar chart without using the date field as the X-Axis in the visual. Then you can create however many date groupings you would want. One thing to remember, the Y-Axis will need to be aggregated as a min in the field wells to ensure repeat values are not being aggregated together. This should get you the expected result, but let me know if you have any questions!