Re: Flag for StartDate and EndDate - Qlik Community

beck_bakytbek · ‎2018-03-05

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

teiswamsler · ‎2018-03-05

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

beck_bakytbek · ‎2018-03-05

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 · ‎2018-03-05

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

beck_bakytbek · ‎2018-03-05

Hi Wasler,

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