Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mslottje
Not applicable

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!Smiley Happy

Mike

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Sales today expression set-analysis

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

12 Replies
oknotsen
Not applicable

Re: Sales today expression set-analysis

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
Not applicable

Re: Sales today expression set-analysis

Hi Mike,

Maybe try this:


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

Thanks,

Carlos M

sunny_talwar
Not applicable

Re: Sales today expression set-analysis

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
Not applicable

Re: Sales today expression set-analysis

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
Not applicable

Re: Sales today expression set-analysis

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
Not applicable

Re: Sales today expression set-analysis

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!Smiley Happy

sunny_talwar
Not applicable

Re: Sales today expression set-analysis

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
Not applicable

Re: Sales today expression set-analysis

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
Not applicable

Re: Sales today expression set-analysis

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..