How to write syntax correctly IF and set expression referring to a Date

[Applicable88]

Hello,

for instance I have this table:

Year		Sales
09.2020		100
10.2020		200
11.2020		200

I need a KPI Window to always show me the sales of that particular month it currently is. 

In September, show 100, in October 200 and so forth.

Can it be implemented via a today() function? or what other options do I have? 

And express it in in a set anaylist , and also in a IF-function?

Please provide me several  options and examples.

Thank you in advance.

[JustinDallas]

Make a Date Flag in your datamodel using InMonth

If( InMonthToDate(CalDate,Today,0) , 'Y', 'N' ) As 'InMTD'

Then in your KPI component, you would have

SUM({<InMTD={'Y'}>} Sales)

[pedrobergo]

Hi @Applicable88 

First, your field year is not a year is a MonthYear using a point delimiter. Maybe you need to change it to a Date format, using these statement inside the script, at the load statement:

MakeDate(Subfield(Year,'.',2),SubField(Year,'.',1),1) as Date

After you did that, you can create a KPI to show the current month sales can be:

• Sum({<Date={">=$(=MonthStart(Max(Date)))<=$(=MonthEnd(Max(Date)))"}>} Sales )

There is some other ways to do that, like create a flag for current month, but this will work to you

Good luck,

Pedro

[Applicable88]

Hi Perdrobergo!

you make a good point. I think the format is a big issue. Even I changed the column to a "Date" with a "12" in thecolumn header  its still don't recognize it.  

So I already managed to add one more column through "adding calculated column"  with an Date(date#) function like this: 

Year		Sales
1.1.2020		100
1.1.2020		100
1.2.2020		200
1.2.2020		200
1.3.2020		600
1.3.2020		600

As you can see he always put an 01 as date. Will it be recognized as date format without problem?

In this case what kind of if or set analyst expression I can use to return the value ONE time from sales. As you can see the sales is always the same value in that same month. I don't want it to get sum. For instance, January I just want the KPI show me '100' .

Hope you can show me. 

Thanks!

[pedrobergo]

Hi @Applicable88 

To garantee the Data field has the correct format, go to Data Model Viewer and point the Year field, after this the section at bottom of screen will show the field type besides Tags line. 

And to correct the KPI, you may know it is allways a metric, so you need to use a formula ever, like sum, max, min, avg and others.

In your case, how the column has the same Sales value to all month records, you just use max, min or avg instead sum and they will show correctly.

Stay safe you too,

Pedro 

[Applicable88]

Hello,

can  you please explain in more detail and using my expressions to show me that? I don't know where you implement the "flag"

Thanks. 

[Applicable88]

Hi,

can you go further into it? I don't know exactly what you mean.

The sample table I put in, is actually a extract of a much bigger table. This table has many other Date correlated columns  but the Revenue_Target is only important to tight to the Revenue_Month. 

[Applicable88]

Hi @pedrobergo ,

I tried everything you said.

I did that in the script: MakeDate(Subfield(Year,'.',2),SubField(Year,'.',1),1) as Revenue_Date2

So it generated me a second column but that time as a "date" and not general. 

sum({<Revenue_Date2={">=$(=MonthStart(Max(Revenue_Date2)))<=$(=MonthEnd(Max(Revenue_Date2)))"}>}Revenue_Target)

Still got zero return. 

What is the problem.?Just cannot figure it out.