Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
mslottje
Creator II
Creator II

Sales today expression set-analysis

Hi guys,

I want to show the sales for the current day/month/quarter/year. In this example, sales is called [Nett Amount].

The following expression in set-analysis works (i.e.) for sales per day:

Sum({1<InvoiceYear={'2017'}, InvoiceDate={'2-1-2017'}>}[Nett Amount])

To make sure that this expression will also be working in the other months/years, I tried for example this expression for my daily sales:

Sum({$<InvoiceDate = {"$(=Date(Today(), 'DD/MM/YYYY'))"}>} [Nett Amount])

This expression is only giving me 0 values.

Could you guys help me out with this one?

Thanks in advance!:)

Mike

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Sum({$<InvoiceDate = {"$(=Date(Today(), 'D-M-YYYY'))"}>} [Nett Amount])

You need to use the correct format for the set analysis to work. Also, look here:

Dates in Set Analysis

View solution in original post

12 Replies
oknotsen
Master III
Master III

If "InvoiceDate" is an actual date (so stored as a number), the Date() function should not be needed. The Date() function is just a "makeup" function anyway, so I would assume you had Date#() in mind anyway.

Does that get you in the right direction?

May you live in interesting times!
CarlosAMonroy
Creator III
Creator III

Hi Mike,

Maybe try this:


sum({<InvoiceDate={"=$(=Date(Today(),'DD/MM/YYYY'))"}>}[Nett Amount])

Thanks,

Carlos M

sunny_talwar

Try this:

Sum({$<InvoiceDate = {"$(=Date(Today(), 'D-M-YYYY'))"}>} [Nett Amount])

You need to use the correct format for the set analysis to work. Also, look here:

Dates in Set Analysis

mslottje
Creator II
Creator II
Author

The InvoiceDate is extracted as an number and eventually transformed into a date (DD-MM-YYYY). So as far as I understand, the Date function is completely useless in this example. Am I right?

Regarding to my issue, I still don't really get why the today() function does not work?

mslottje
Creator II
Creator II
Author

Hi Carlos,

thanks for your reply!

The expression you've sent me, is giving me the total sales over all time. So that did not really worked as expected.

Thanks,

Mike

mslottje
Creator II
Creator II
Author

Hi Sunny,

I'm still getting 0 values with this expression..

In the master calendar, the date format is (DD-MM-YYYY), so that shouldn't be the problem.

I will have a look at the blog you recommended though!:)

sunny_talwar

Try with this then

Sum({$<InvoiceDate = {"$(=Date(Today(), 'DD-MM-YYYY'))"}>} [Nett Amount])

Also, replicate your other expression to see if the hardcoded value matches the expression value or not

Sum({1<InvoiceYear={'2017'}, InvoiceDate={"$(=Date(Today(), 'DD-MM-YYYY'))"}>}[Nett Amount])

Also, make sure that you do have data for today in your application.

CarlosAMonroy
Creator III
Creator III

Did not notice the date format. You have to use the correct one:

InvoiceDate={'2-1-2017'}

InvoiceDate={"$(=Date(Today(),'D-M-YYYY'))"}>}


Thanks,

Carlos M


mslottje
Creator II
Creator II
Author

hmmm, that shouldn't be the problem,

As mentioned in my reaction to Onno, I extracted the data as numbers.

I transformed these numbers into a date with DD-MM-YYYY as format.

With this info, it is not fully clear for me why the today function does not work.

Next to that, it's quite strange that a date is presented as 1-1-2017 and not 01-01-2017.

Perhaps I should have a look in to my data first..