topic Re: Using formula to set data as filter in Set Analysis in App Development

Using formula to set data as filter in Set Analysis

FlaminioIta88 — Wed, 19 Apr 2023 10:42:52 GMT

Hi,

I am struggling trying to set a formula that I created as a filter.

My Goal: my current Sum formula should sum ONLY the value of the Current Year.

My formula (before filling with filter about the year):

Sum( {$<
[IDProduct]={1,3,4,5,6},
[IDCustomer]={1}
>}
[Sales] )

So, I create a Formula called vCurrentYear in this way

year(today())

It works fine, if I put it in a KPI it results in 2023.

I am not able to fill it as a filter in the first formula. I tried several ways without success.

How should I write it?

Thanks

Re: Using formula to set data as filter in Set Analysis

FlaminioIta88 — Wed, 19 Apr 2023 10:47:06 GMT

I have ound some solutions in other topics like this https://community.qlik.com/t5/New-to-Qlik-Sense/How-to-write-set-analysis-for-display-current-year/td-p/89134, but it doesn't show how to mix this filter with my other already existing filters

Re: Using formula to set data as filter in Set Analysis

Brunei — Wed, 19 Apr 2023 12:09:43 GMT

You can try to use this formula, you have to substitute only DATE_FIELD with the field in your data set that contein date:

Sum( {$<[IDProduct]={1,3,4,5,6},[IDCustomer]={1} ,DATE_FIELD=">=$(=Date(YearStart(Max(DATE_FIELD)),'DD/MM/YYYY'))"}>} [Sales] )

Re: Using formula to set data as filter in Set Analysis

Gabbar — Wed, 19 Apr 2023 12:17:07 GMT

I am hoping that you have a field Year in your Data Model and Your DateKey is related to that year
Try this:
Sum( {$<
[IDProduct]={1,3,4,5,6},
[IDCustomer]={1},
year={"=year(max(DateKey))"}
>}
[Sales] )

where DateKey is you Date Field.