Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I do have the following expression :
Sum( {1<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}>} Sales)
=> It sum the today's sale, regardless of the filter I use (thanks to the identifier "1")
What I need is that this formula uses the filter "SITE", but not any filters done on the date (if any).
Is it possible? How can I do that?
Thank in advance,
MT
so you only want to ignore Date, but leave the other selections as they are ?
try with Date =
Try this:
Sum( {1<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}, SITE = P(SITE)>} Sales)
Hello Sunny,
It is working fine. Thank you !
What if now, I would like that all my filter are working except the filter on the date ?
Thank you in advance,
MT
Try this:
Sum({<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}, Date = >} Sales)
Sorry, it does not work.
I tried Sum({<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}, DateId = >} Sales), but in this case, I get the Sales for all the date (and not just the today's sales which is why I used {1<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}>}.
We already have a DateId filter in our set analysis and any selection in DateId should not have any impact on this expression:
Sum({<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}>} Sales)
adding a 1 there won't restrict your dates to today's date, but this does {<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}>}
Not sure what exactly is the issue. Would you be able to share a sample?
If I do not add the identifier 1, then by default, the identifier used by Qlik is $, meaning that the result will be based on the current selection (using filters) while 1 will use my whole dataset regardless of my filter use.
Therefore Sum({<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}>} Sales) will return 0 if today's date is not part of my current selection.
I would like the sales to be calculated only for today's date (regardless of my date filter), but using the other filters I could have.
In this scenario, Sum( {1<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}, SITE = P(SITE)>} Sales) is working fine, but only the SITE filter will be taken into account, while I also have some other filters the customer can use (CATEGORY, BRAND, etc...). Of course, I guess that I could add as exception all these filters, something like this :
Sum( {1<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}, SITE = P(SITE), CATEGORY = P(CATEGORY), BRAND = P(BRAND)>} Sales)
But, I will have to update my formula if I add new filters.
In fact, to resume, I need the sum of sales using filters except for the date wich is fixed.
Do not hesitate if you need more precision.
Warm Regards,
MT
You need to clear or set all calendar fields the user may select a value on, assuming DateID, Date, Week, Month, Year field:
Sum({<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}, Date, Week, Month, Year >} Sales)