Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
swuehl
MVP
MVP

Андрей Шепель 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

View solution in original post

7 Replies
Anonymous
Not applicable
Author

Try: Min(Month(Date))

see attached QVW

Anonymous
Not applicable
Author

thanks, but it's not working(

try attached file

Anil_Babu_Samineni

This?

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

yes, but you created additional field Month for that.

Ralf_Heukäufer
Partner - Creator III
Partner - Creator III

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')



swuehl
MVP
MVP

Андрей Шепель 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
Anonymous
Not applicable
Author

Thanks! it works perfect!