Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Hannah94
New Contributor III

Re: Today() in Set Analysis

Yes, I want to ignore the selection in Testdatum field .
Unfortunately it doesn't work either.
So the $ identifier works with the other fields, but not with the Testdatum field.
I think that function : =Avg({$<F99 = {1}, Testdatum = {"=Year(Testdatum) < Year(Today())"}>} Ergebnis) is the best so far.
It ignores 2019. That's good. But it is still necessary to choose the other years in the field. So he only calculates the selected ones.

Thanks a lot for your effort so far. 🙂

Re: Today() in Set Analysis

You really have me confuse now... You are saying that you want to ignore selection in Year(Testdatum), but the expression should change when you select 2018? I don't think I follow you. Would you be able to share a sample to show us the issue?

Hannah94
New Contributor III

Re: Today() in Set Analysis

No, the line shouldn't change with the selection.

I had to hide some Information but took some examples together in a word document. First how it works with F99 and then how it doesn't with Testdatum. I've put some explanations in it as well. I hope it's more understandable now.  

Re: Today() in Set Analysis

Do you understand what this is saying?

=Year(Testdatum) < Year(Today())

Show all years which are less than Today's year.... which is 2019.... but this is based on selection... if you don't want selection to impact this... than try this

=Year(Only({1} Testdatum)) < Year(Today())

 By adding Only({1} ... ) you are ignoring selection of any sorts to the Testdatum field within the set analysis... but having said that... did you ever create the year field in the script? if you did... why don't you try this

Year = {"<$(=Year(Today()))"}, Testdatum

Also, make sure to change your filder for Year to be Year field rather than Year(Testdatum)... also suggest to do the same thing for Month by creating a month field in the script rather than using Month(Testdatum) filter on the front end

Hannah94
New Contributor III

Re: Today() in Set Analysis

Hey,

that one:

'=Avg({< F99 = {0}, KdBtLf={'Mustermann'}, Jahr={"<$(=Year(Today()))", Testdatum } >}Ergebnis'  works.

Thanks a lot! 🙂

Would be best if I could exclude the month field selection  as well. Is that possible? I tried to edit this function but couldn't make it work with month.  In the meanwhile I got a autocalendar via Data Manager. I use it with the other diagrams now. Seems to be more stable than  my own

"Year(Testdatum) as Jahr,
Month(Testdatum) as Monat "

in the script. Maybe using the autocalendar is an option too? I couldn't make it work so far. I tried to replace "Testdatum" with "[Auswertung.Testdatum]" and "Jahr" with  "[Auswertung.Testdatum.autoCalendar.Year]".

Could you please explain to me why  "=Avg({$< Versuchstyp= {Berstkraft}>}Ergebnis)" and  "=Avg({$< F99= {0}>}Ergebnis)" worked? This functions weren't based on selection. They ignored the Versuchstyp and the F99 Selection. I don't get, why the $ Identifier worked with this filders but didn't with Testdatum. I thought  the $ Identifier isn't based on selection because with all the others it wasn't when i was testing.

Thanks
Hannah

Highlighted
Hannah94
New Contributor III

Re: Today() in Set Analysis

In case someone has a similar issue. Here is my solution:

Instead of two filters with Year and Month I have a single one now called TestmonatMJ.

I loaded it in the script with:

Date((Monthstart(Testdatum)), 'MM.YYYY') as TestmonatMJ

It looks like:

Filter.png

My positionline has this function:

=Avg({< F99 = {0}, KdBtLf={'Referenzlieferant'}, TestmonatMJ={"<$(=date(yearstart(Today()),'MM.YYYY'))"}>}Ergebnis)

It ignores the F99, the KdBtLf and the TestmonatMJ filter and shows to me the averange result (Ergebnis) before 01.01.2019. I think yearstart() works a lot better than year() in this case.

My second positionline is pretty much the same but shows me the averange result before this month:

=Avg({< TestmonatMJ ={"<$(=date(monthstart(Today()), 'MM.YYYY'))"}, F99 = {0}, KdBtLf={'Referenzlieferant'}  >}Ergebnis)

That's a example  with 05.2019 selectet. Now I can compare the averange of this month (orange positionline) with the averange of the past perfectly. The blue and the pink line don't move with the date selection. The orange one does.

Result.png