Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Return only Sales of the current month in KPI Window

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_MonthRevenue_Target
05.202028170000
05.202028170000
05.202028170000
06.202030000000
06.202030000000
06.202030000000
06.202030000000

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_MonthRevenue_Target
01.05.202028170000
01.05.202028170000
01.05.202028170000
01.06.202030000000
01.06.202030000000
01.06.202030000000
01.06.202030000000

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!

3 Replies
Highlighted
Specialist
Specialist

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.

Creator II
Creator II

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. 

Highlighted
Specialist
Specialist

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