Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

andreyfcdk91
New Contributor III

Identify which dates are the first in the month

Hi everyone!

Is there in QV some solution to identify which dates are the first in the month?

I have temp table and need to check in expr if date is the first in the month. ! ! !

Need to solve this one in expression.

Thanks.

1 Solution

Accepted Solutions
MVP
MVP

Re: Identify which dates are the first in the month

Андрей Шепель wrote:

yes, but you created additional field Month for that.

And what's the problem with that?

The expression should still be dynamic when filtering on dates, if that is your concern.

At least, you should have used a correct format code for your dates, currently, the values are not correctly read in as dates.

Attached a version with a chart only solution, not creating a new field and not fixing your date issue (so not touching your script), but that makes it more complicated than necessary.

Note the expression and the sort expression needed to fix your data issue.

Date If(Monthstart(Above(Date#(Date,'D/M/YYYY'))) <> Monthstart(Date#(Date,'D/M/YYYY')),1,0)
0
4/09/20171
11/09/20170
18/09/20170
25/09/20170
30/09/20170
2/10/20171
9/10/20170
16/10/20170
23/10/20170
30/10/20170
31/10/20170
6/11/20171
13/11/20170
20/11/20170
27/11/20170
30/11/20170
4/12/20171
7 Replies
aronwilliamson
Contributor

Re: Identify which dates are the first in the month

Try: Min(Month(Date))

see attached QVW

andreyfcdk91
New Contributor III

Re: Identify which dates are the first in the month

thanks, but it's not working(

try attached file

Re: Identify which dates are the first in the month

This?

Capture.PNG

Life is so rich, and we need to respect to the life !!!
andreyfcdk91
New Contributor III

Re: Identify which dates are the first in the month

yes, but you created additional field Month for that.

Ralf_Heukäufer
Contributor III

Re: Identify which dates are the first in the month

Hello,

first you need to create a own column for Month in your Loadscript.

Looks like:

[Sheet1]:

LOAD

    Date(Date#([Date], 'D/MM/YYYY') ) AS [Date],

   Month (Date(Date#([Date], 'D/MM/YYYY') )) as MyMonth

FROM [lib://FilePath/Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Then you can use it in an Expression by aggregate your Data with:

=aggr(min(Date),MyMonth)

Looks like:

When you want to use it in a form like your table you can use:

=if (aggr(min(Date),MyMonth)=Date,'1','0')



MVP
MVP

Re: Identify which dates are the first in the month

Андрей Шепель wrote:

yes, but you created additional field Month for that.

And what's the problem with that?

The expression should still be dynamic when filtering on dates, if that is your concern.

At least, you should have used a correct format code for your dates, currently, the values are not correctly read in as dates.

Attached a version with a chart only solution, not creating a new field and not fixing your date issue (so not touching your script), but that makes it more complicated than necessary.

Note the expression and the sort expression needed to fix your data issue.

Date If(Monthstart(Above(Date#(Date,'D/M/YYYY'))) <> Monthstart(Date#(Date,'D/M/YYYY')),1,0)
0
4/09/20171
11/09/20170
18/09/20170
25/09/20170
30/09/20170
2/10/20171
9/10/20170
16/10/20170
23/10/20170
30/10/20170
31/10/20170
6/11/20171
13/11/20170
20/11/20170
27/11/20170
30/11/20170
4/12/20171
andreyfcdk91
New Contributor III

Re: Identify which dates are the first in the month

Thanks! it works perfect!

Community Browser