How do I use IF to align data?

Some formulas are used more often than others when building Klips. In this series of articles, a Klip is built using a common set of formulas to give a better understanding of how these formulas work within a sample data set.

This Klip counts the number of Yes and No responses per date and the number of items sold for Yes responses using separate Series and conditions. The IF function is used to ensure the count of records remains aligned.

This Klip uses the following data source:

Step 1: Building the X Axis of a Bar/Line Chart component

Use the Bar/Line Chart component and a data source to start building your Bar chart. The dates from your data will make up the X Axis.

Select your dates, in this example column A (A:A), then use the group option in the Properties panel or the GROUP function to group the dates so each date appears as one label. 

Step 2: Building the Series of a Bar/Line Chart component

Use two Series components to display the survey responses. Both components use formulas with IF and the survey response values in column B (B:B) to create a condition. BLANK is returned for any data that is false in the condition. 

Series 1: No

IF(values="No", values, BLANK())

Series 2: Yes

IF(values="Yes", values, BLANK())

Use the Aggregation method, Count, in both Yes and No Series to count the amount of responses for each date. The data is correctly aligned because the Count aggregation does not count BLANK data. 

Series 3:

Use an additional Series component to display the total number of items sold per date. Select the column of data that lists the amount of items sold, in this example, column C (C:C)

Use the Aggregation method, Sum, to sum the amount of responses for each date. The data is correctly aligned because the Sum aggregation does not count BLANK data.