Set analysis only using some of the filter (and not all or none)

[Anonymous]

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

[ramoncova06]

so you only want to ignore Date, but leave the other selections as they are ?

try with Date =

[sunny_talwar]

Try this:

Sum( {1<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}, SITE = P(SITE)>} Sales)

[Anonymous]

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

[sunny_talwar]

Try this:

Sum({<DateId={'$(=Date(vToday,'DD/MM/YYYY'))'}, Date = >} Sales)

[Anonymous]

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'))'}>}.

[sunny_talwar]

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?

[Anonymous]

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

[swuehl]

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)