Skip to main content

Comparison Display of Data After Selected Month

Background

A table has fields "FG code", "Channel", "Year&Month" and "Sellin" (where the primary key is FG code, Channel, Year&Month). We want to display the value of "Sellin" and hope to control the display start time (M0 Month) through filtering, while the end time of the display column is always 2020-12.

Implementation Effect

image.png

Implementation Idea

  1. This type of table is suitable for using the "Comparison" function to display.

  2. However, since there is only one time dimension (Year&Month), we need to add another time dimension (M Year&Month) as the column participating in the comparison.

Implementation Method

  1. In ETL, use grouping aggregation to calculate the time span of Sellin for each product determined by FG code and Channel.
    (To prevent too many dimensions, for example, if one product has been sold since 1900, but other products only started selling this year, we don't need to add time dimensions from 1900 to present for products that only started selling this year).

image.png

  1. Use the method of associating with the time span to add the time span fields Min Year&Month and Max Year&Month.

image.png

  1. Add a time field for comparison, using functions to automatically complete the calendar.
    Here Max Year&Month can be changed to your required end time. I didn't change it here because Max Year&Month equals 2020-12.
    Tip: explode(sequence([start date], [end data], interval 1 month)) where interval 1 month can also be replaced with interval 1 week, etc.

image.png

  1. Since we only need to display months after the time selected by the customer filter for comparison, in the card edit page, create a field to control whether to display, and drag this field into the filter bar.

image.png