Fix Previous Year comparison

[janbolender]

Hello everybody,

I am new in this forum, but I need help.

I am not new into Qlik Cloud, but this kind of problem is driving me crazy.

In this formula is simply want to show the Monday of the last completed Week, independent of all filters (I use the "1").

Count({1<DATUM_DAY_ALL={"=$(=WeekStart(Today(),-2))"}>} Tickets)

The KPI shows me "0" eventhough I have Values for this day in my raw data.

I already had problems with this kind of exact date entry (like "=10.08.2026") and then I used an area comparison instead (like ">=10.08.26<17.08.26") an then it worked.

Can anyone help me with my problem?

Thank you in advance 🙂

[æden]

If your date values doesn't match, there should be a data/format mismatch:

DATUM_DAY_ALL values could be 46567.123456 but it shows 11.05.2026 (10:23:59) and hides it's time value. WeekStart() gives 46567.000000 so they don't match and the letter formula works because it is a range between 46567 and 46568

You can fix it by formatting both values to be same like Date(WeekStart(Today()),'DD.MM.YYYY') if your DATUM_DAY_ALL is formatted like 'DD.MM.YYYY'.

If they still doesn't match try floor() for both values to get rid of time values.

[janbolender]

Thanks for your replay.

But even with floor or date it doesnt work.

DATUM_DAY_ALL is already just a Date format. I use it for my master calender. So it just contains real date vlaues.

If i try like that: DATUM_DAY_ALL={"=$(=Floor(Date(WeekStart(Today(),-2),'DD.MM.YYYY')))

i get all values back that are in my raw data ( 15.71M). 

[æden]

Can you drag and drop your DATUM_DAY_ALL field into a table as dimension and send screenshot?

Also you can add your formula as a measure to same table for comparison.

[Daniel_Castella]

Hi @janbolender 

I think there are two issues on your formula. First of all, if you want to see the data for Monday of the previous week, it should be a -1, not a -2. So, the WeekStart function will provide you the current week (it is the same than setting a 0). If you want the previous, you set -1. If you want two weeks prior, -2... etc.

The second issue I see is that the first = inside the set expression. To avoid confusions, I think the formula should be: 

Count({1<DATUM_DAY_ALL={"$(=WeekStart(Today(),-1))"}>} Tickets)

As you have it in your formula, you obtain {"=18/08/2026"} instead of {"18/08/2026"}. A value beginning with = is interpreted as an advanced search expression rather than a literal value. Qlik tries to evaluate the expression after the = for each possible field value. 

It works when you use ">=10.08.26<17.08.26" because then the equal is relating to the >, not that advanced search.

Please, let me know if this solves your issue.

Kind Regards

Daniel

[marcus_sommer]

An addition to all the other suggestions - the call of:

{< Field = {Value}>}

means that the comparisons is made against the string-representation of the values - even by numbers like dates. The consequence is that not only the values on both sides needs to be matching else also their formatting. The only way to enforce a numeric comparison (by of course numeric values) is to apply something like yours: {">=Value1<=Value2"}.

Best practice is to avoid all such trouble and providing all relevant number-fields (especially the entire calendar-stuff) also as pure numeric fields within the data-model and all kinds of calculations happens against them. It needs not much efforts, enables further the possibilities to use a string-representation as a dimension, apply any selection against a formatted number it and doing the final comparions against a pure numeric value of it.