topic Re: hide rows where measure is null if future in App Development

hide rows where measure is null if future

jalanhart — Tue, 25 Feb 2025 01:33:58 GMT

I am trying to show sales and budgets by quarter year increments and I want to hide these future rows where there is a measure value for budget but not for sales obviously. How do I remove them? I was thinking something like "if yearquarter is > today, hide" or something like that.

Re: hide rows where measure is null if future

Chanty4u — Tue, 25 Feb 2025 02:44:10 GMT

Try this

If(

YearQuarter <= Year(Today()) * 100 + Ceil(Month(Today()) / 3)

OR NOT IsNull(Sum(Sales)),

YearQuarter

)

Re: hide rows where measure is null if future

diegozecchini — Tue, 25 Feb 2025 08:12:39 GMT

Hi!
If you want to filter out future data in the data load itself, you can add a condition to your WHERE clause like this "WHERE YearQuarter <= Num(Today())"

If your dataset doesn't have a direct YearQuarter numeric comparison with today's date, you might extract the year and quarter separately:

WHERE MakeDate(LEFT(YearQuarter,4), MID(YearQuarter,5,2)*3-2, 1) <= Today()
This converts YYYYQ format into a valid date and compares it.

Re: hide rows where measure is null if future

SRA — Tue, 25 Feb 2025 13:19:19 GMT

Hi,

I would recommend to use a Set Analysis in both measure (budget and sales) to cap your measure to the period you want (max sales date or today rounded to QuarterEnd?)

Regards

Re: hide rows where measure is null if future

marcus_sommer — Tue, 25 Feb 2025 13:25:17 GMT

Beside restricting the data-set per where-clause or the object-dimension with an if-condition you may adjust the expression with a set analysis with a reference to the date, for example:

sum({< Date = {"<=$(=today())"}>} Value)