KPI window return the Data according to the actual month of TODAY

[Applicable88]

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!

[Lisa_P]

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)

[Applicable88]

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? 

[Lisa_P]

Are you creating this Umsatzmonat(Datum) field in the load script or in the chart ?

[Applicable88]

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.