Forum Discussion
IF calculated column
Hi good day, can anyone help me on can i achieved the result i want. i have a table
-IF the date is lessthan today and the detailed column is blank change "red" my marker
-IF the date is near (30 days) and the deatailed column is blank change "red" my marker
-IF the date is morethan (30 days) from taday and the detailed column is blank change "Black" my marker
-IF the detailed column text = Actual Recieved, Done change "Green" my marker
-IF the detailed column text = Updated date and the date is morethan (30 days) from today change "Black" my marker
-IF the detailed column text = Updated date and the date is lessthan (30 days) from today change "Red" my marker
Thank you
AllanBerces , I thought you are using Table , you can create a calulated column for line chart by going to modelling tab and select new column
Color =
SWITCH(
TRUE(),
AND([Date] < TODAY(), ISBLANK([Event])), "Red",
AND([Date] <= TODAY() + 30, ISBLANK([Event])), "Red",
AND([Date] > TODAY() + 30, ISBLANK([Event])), "Black",
OR([Event] = "Actual Received", [Event] = "Done"), "Green",
AND([Event] = "Updated date", [Date] > TODAY() + 30), "Black",
AND([Event] = "Updated date", [Date] <= TODAY() + 30), "Red",
"DefaultColor" -- Default color if none of the conditions match
)Go to the Report view and select your line chart.
In the Visualizations pane, drag the new Color column to the Legend field well.
This will create separate lines for each color category.
Customize the Colors:Click on the line chart to select it.
In the Visualizations pane, click on the Format (paint roller) icon.
Expand the Data colors section.
Assign the desired colors (Red, Black, Green) to the corresponding categories.
4 Replies
In the Visualizations pane, click on the down arrow next to the column you want to format (e.g., the date column or the detailed column).
Select "Conditional formatting" and then "Background color" or "Font color" depending on your preference.
Set Up Conditional Formatting Rules:In the Conditional formatting window, click on "Advanced controls" to open the detailed formatting options.
Add Rules:Add the following rules one by one:
Rule 1: Date is less than today and detailed column is blank
Format by: Rules
Based on field: Select the date column
Rule:
If date is less than today() and detailed is blank, set the color to red.
Use a custom formula to check if the detailed column is blank.
Rule 2: Date is within 30 days and detailed column is blank
Format by: Rules
Based on field: Select the date column
Rule:
If date is within the next 30 days and detailed is blank, set the color to red.
Use a custom formula to check if the detailed column is blank.
Rule 3: Date is more than 30 days from today and detailed column is blank
Format by: Rules
Based on field: Select the date column
Rule:
If date is more than 30 days from today and detailed is blank, set the color to black.
Use a custom formula to check if the detailed column is blank.
Rule 4: Detailed column text is "Actual Received" or "Done"
Format by: Rules
Based on field: Select the detailed column
Rule:
If detailed column text is "Actual Received" or "Done", set the color to green.
Rule 5: Detailed column text is "Updated date" and date is more than 30 days from today
Format by: Rules
Based on field: Select the date column
Rule:
If detailed column text is "Updated date" and date is more than 30 days from today, set the color to black.
Rule 6: Detailed column text is "Updated date" and date is less than 30 days from today
Format by: Rules
Based on field: Select the date column
Rule:
If detailed column text is "Updated date" and date is less than 30 days from today, set the color to red.
Apply the Rules:
After setting up each rule, click "OK" to apply the rule.
Repeat the process for each rule.HI bhanu_gautam thank you for the reply but i try to find the solution you suggest, but no luck. Im using line chart, just renam the detailed to event and only the below list when i click the down arrow.
Thank you
AllanBerces , I thought you are using Table , you can create a calulated column for line chart by going to modelling tab and select new column
Color =
SWITCH(
TRUE(),
AND([Date] < TODAY(), ISBLANK([Event])), "Red",
AND([Date] <= TODAY() + 30, ISBLANK([Event])), "Red",
AND([Date] > TODAY() + 30, ISBLANK([Event])), "Black",
OR([Event] = "Actual Received", [Event] = "Done"), "Green",
AND([Event] = "Updated date", [Date] > TODAY() + 30), "Black",
AND([Event] = "Updated date", [Date] <= TODAY() + 30), "Red",
"DefaultColor" -- Default color if none of the conditions match
)Go to the Report view and select your line chart.
In the Visualizations pane, drag the new Color column to the Legend field well.
This will create separate lines for each color category.
Customize the Colors:Click on the line chart to select it.
In the Visualizations pane, click on the Format (paint roller) icon.
Expand the Data colors section.
Assign the desired colors (Red, Black, Green) to the corresponding categories.
