Today() in Set Analysis

[Hannah94]

Hey guys,

I know there are a lot of posts about this topic but I still can't make my visualization work.

I have a distribution diagram and want to add a position line that shows the averange result of tests of  all time (until last month) and ignores the selected time in the time filter. The other part of the diagram is supposed to show the data that is selected in the filter. For example of the current  month. The other filters are supposed to change the position line and the other part of the diagram. The time filter has the function: =Year(Testdatum).

 
Unfortunately all my attempts didn't work. I think it's because of the format of the date. 

My current version is this one:

=Avg({$< [Year(Testdatum)]={"$(=num(date(today())))"}>}Ergebnis)

It shows me a line but it doesn't ignore the time selection. And it isn't included that the current month shouln't be included.

Unfortunately I haven't safed the other attempts. I tried to include formats like "MM.YYYY" or "DD.MM.YYYY 00:00:0000" as well.  Some functions showed me lines but none ignored the time selection.


I would really appreciate if you could help me.  I hope my english is understandable.

Thanks

Hannah

[Hannah94]

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. 🙂

[sunny_talwar]

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]

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.  

[sunny_talwar · Accepted solution]

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]

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

[Hannah94 · Accepted solution]

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:

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.