Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

beck110979
Valued Contributor III

Flag for StartDate and EndDate

Hi Folks

i got a question, my table does look like:

Project     ReportDate     EndDate      Difference in Months

A            31.03.2018     30.06.2021          39 Months

i am using this procedure:

test:

LOAD *,

$(MonthDiff(Start, EndDate)) AS MonthsDifference;

load *,

MonthStart(ReportDate-1, 1) as Start

inline [

Projekt, ReportDate, EndDate

A, 31.03.2018, 30.06.2021

The output does look like:

Project    Start                         End                         Month

A             01.04.2018               31.12.2018                  9

A             01.01.2019               31.12.2019                 12

A             01.01.2020               31.12.2020                 12

i calculated the Monthdifferences and it does work great, regarding this issue, i have a question, how can i add the flags to EndDate and StartDate, for instance:

31.12.2017 = Ending_OldYear

31.01.2018 = Beginning_NewYear

31.12.2018 = Ending_OldYear

31.01.2019 = Beginning_NewYear

Flag = 1 for Ending_OldYear, 2 = for Beginning_NewYear

Does anybody have any idea?

Thanks a lot

Beck

4 Replies
teiswamsler
Contributor II

Re: Flag for StartDate and EndDate

Hi Beck

I am not sure what u are trying to achieve.

But mapping de specific date may solve your issue.

If( Floor(End) = Floor(YearEnd(End), 1, // Numeric Year End Date

     If( Floor(End) = Floor( monthend(YearStart(End))), 2 )) // Numeric Year Start First Month End Date

as Flag

/teis

beck110979
Valued Contributor III

Re: Flag for StartDate and EndDate

Hi Wamsler,

first of all thanks a lot for your responce and your time,

my issue is: as you have seen, i created the time - intervalls like:

01.12.2017 - 31.12.2017

01.01.2018 - 31.12.2018

i want to add the Flags to the data like: 01.01.2018 = Beginning

                                                          31.12.2018 = End

and if i compare the 31.12.2017 with 31.01.2018 so i want to achieve:.

31.12.2017 = End

31.01.2018 = Beginning

teiswamsler
Contributor II

Re: Flag for StartDate and EndDate

Hi beck

then use same metode as before but

If( Floor(End) = Floor(YearEnd(End), End, // Numeric Year End Date

     If( Floor(End) = Floor( YearStart(End)), Beginning )) // Numeric Year Start Date

as Flag

beck110979
Valued Contributor III

Re: Flag for StartDate and EndDate

Hi Wasler,

i implemented your way, but it does not work, do you have an example?