Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I had a big problem with a column called "Umsatzmonat". Its a revenue month.
Table looks like this:
Umsatzmonat | Umsatzmonat(Datum) | Umsatzziel |
04.2020 | 01.04.2020 | 100000 |
04.2020 | 01.04.2020 | 100000 |
04.2020 | 01.04.2020 | 100000 |
05.2020 | 01.05.2020 | 5000000 |
05.2020 | 01.05.2020 | 5000000 |
05.2020 | 01.05.2020 | 5000000 |
06.2020 | 01.06.2020 | 650000 |
06.2020 | 01.06.2020 | 650000 |
06.2020 | 01.06.2020 | 650000 |
Because of the problem I created a additional column called "Umsatzmonat(Datum). Someone gave me the expression
for that calculated field: date(date#(Umsatzmonat,'MM.YYYY)) since the format is "MM.YYYY " I don't know why it gives me the dates in that format for example: 01.06.2020?
Still now I have a "date column" to use.
I need an KPI to show me always the value in the cell of "Umsatzziel"of the month NOW. Means ...in April 100000, in May =5000000 and so forth....(Not the sum of all values of a month, just one time!)
I used something like this: if({<[Umsatzmonat(Datum)]={"$(=Month(today()}}", Umsatzziel)
I tried many variants, but still just get zero or syntax errors.
I hope someone can tell me the right function and also explain why my date always start with the the first of a month (01.05.2020) instead of MM.YYYY (05.2020)
Thanks in advance and stay safe!
The first part of your question:
date(date#(Umsatzmonat,'MM.YYYY))
the date#(Umsatzmonat,'MM.YYYY) is telling you how to interpret the date you are loading
the date(..) is telling it to format this as a date, thus your default date format is shown, you could reformat this in the date function or in the properties panel of your table. eg date(date#(Umsatzmonat,'MM.YYYY), 'MM.YYYY')
I think if you are only looking for current month Umsatzziel, you should use this expression:
Sum({<Umsatzmonat={'$(=Max(Umsatzmonat))'}>}Umsatzziel)
Hi Lisa,
something is missing.
When I'm referring to my table to use the middle column, which is identified as Date:
sum({<[Umsatzmonat(Datum)]={$(=Max([Umsatzmonat(Datum)]))}>} Umsatzziel)
Still something is not okay. I also tried yours. But its also not working. Where is the mistake?
Are you creating this Umsatzmonat(Datum) field in the load script or in the chart ?
Hi
I created that column with the date from Umsatzmonat "added calculated field" like this:
So its in the Dataload manager and not with the script. But in the script I set it like german format : DD.MM.YYYY
I now just learned that you cannot "change" a calculated field anymore. Its based on calculation, and cannot set as a Date, General, Timestamp etc.