Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
jalanhart
Creator
Creator

hide rows where measure is null if future

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. 

 

jalanhart_0-1740447220909.png

 

Labels (2)
4 Replies
Chanty4u
MVP
MVP

Try this 

If(

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

    OR NOT IsNull(Sum(Sales)), 

    YearQuarter

)

diegozecchini
Specialist
Specialist

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.

SRA
Partner - Creator
Partner - Creator

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

marcus_sommer

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)