Return only Sales of the current month in KPI Window

[Applicable88]

Hello,

I tried many time to find a solution but its still doesn't work out. I loaded a table with a Revenue_Month field like the following.

Revenue_Month	Revenue_Target
05.2020	28170000
05.2020	28170000
05.2020	28170000
06.2020	30000000
06.2020	30000000
06.2020	30000000
06.2020	30000000

Qlik didn't recognized as a Date field, so I put a additional Date function in the loading script like á la Date(date#(MM.YYYY),'DD.MM.YYYY') as Revenue_Month. Now Qlik know its a date I also got automated.date function to chose from. Since I add the days also, I got the following table: 

Revenue_Month	Revenue_Target
01.05.2020	28170000
01.05.2020	28170000
01.05.2020	28170000
01.06.2020	30000000
01.06.2020	30000000
01.06.2020	30000000
01.06.2020	30000000

Now I want a KPI Window which always shows me the Revenue_Target of my current month. I don't want a sum of the values, so for instance when now its May I just want to display  '28170000' and next month in June it should return '30000000'. 

I tried many variation of set expression combined with like this with and without "distinct": 

=Sum({<[Revenue_Month]={'<=$(=Date(Today()))'}>}distinct [Revenue_Target])

or

Sum({<[Revenue_Month.automated.Month]={'<=$(=Date(Today()))'}>}distinct [Revenue_Target])

also

If-Function like: if (Revenue_Month==$(=Date(Today()), distinct Revenue_Target)) 

never worked out for me. Mostly I got some aggregated big amount but mostly 0 respectively no value at all.

Hope someone has an idea whats going on here. 

Thanks in advance!

[vamsee]

In your expressions, you are comparing Month to date

Change it to Month(Today()), 

in the script

Month(Revenue_Month) as Revenue_Month,

Date(Revenue_Month) as Revenue_Date,

Year(Revenue_Month) as Revenue_Year

Then 

Example: =Sum({<[Revenue_Month]={"=$(=Month(Today()))"}>}[Sales])

On the same note if your dashboard has data over the years you might want to add the Year filter also.

FYI you can use Makedate() function in Qlik to create date when you have month and year.

[Applicable88]

Hi Vamsee,

thanks for your quick reply.

When I have one column called "Revenue_Month", which is already loaded in script and formatted as "DD.MM.YYYY" thus as a DateField ,and through this I also got automated.calendar function (since qlik knows its a date with already day, month, and year in it), why my expression still doesn't work and return with '0' revenue?

Like this:

Sum({<[Revenue_Month.autoCalendar.Month]={"=$(=Month(Today()))"}>}Revenue_Target])

Your suggestion is to  load them separately in the data script , but isn't qlik only compare the automated.month to the month(today()) already? 

And about the makedate() function....for what pupose , when I already have a complete field as a date like: 01.05.2020, 01.06.2020 and so fourt....

Hope you can help me out here. 

[vamsee]

Hi,

All I am trying to say is make sure 

Revenue_Month.autoCalendar.Month has month names as data, like Jan, Feb, Mar

Month(Today()) returns May

As I don't know how you are populating your calendar, so can't help you more with your function as the syntax of the expression looks okay.

---

Yeah, I just suggested it as an alternative to your approach