Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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/t... but it doesn't show how to mix this filter with my other already existing filters
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] )
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.