Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Андрей Шепель 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/2017 | 1 |
11/09/2017 | 0 |
18/09/2017 | 0 |
25/09/2017 | 0 |
30/09/2017 | 0 |
2/10/2017 | 1 |
9/10/2017 | 0 |
16/10/2017 | 0 |
23/10/2017 | 0 |
30/10/2017 | 0 |
31/10/2017 | 0 |
6/11/2017 | 1 |
13/11/2017 | 0 |
20/11/2017 | 0 |
27/11/2017 | 0 |
30/11/2017 | 0 |
4/12/2017 | 1 |
Try: Min(Month(Date))
see attached QVW
thanks, but it's not working(
try attached file
This?
yes, but you created additional field Month for that.
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')
Андрей Шепель 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/2017 | 1 |
11/09/2017 | 0 |
18/09/2017 | 0 |
25/09/2017 | 0 |
30/09/2017 | 0 |
2/10/2017 | 1 |
9/10/2017 | 0 |
16/10/2017 | 0 |
23/10/2017 | 0 |
30/10/2017 | 0 |
31/10/2017 | 0 |
6/11/2017 | 1 |
13/11/2017 | 0 |
20/11/2017 | 0 |
27/11/2017 | 0 |
30/11/2017 | 0 |
4/12/2017 | 1 |
Thanks! it works perfect!