Date Range Field

[kprotrka]

I need to create on filed wit the date ranges.

should be like this:

if(date>31.03.2020 and date<01.04.2021,'old',

if(date>30.09.2020 and date<01.10.2021,'new')

as New_Old

My problem is that i have one period(01.10.2020-01.04.2021)...where it should take both ranges.

I need to do some calculations after(sum,avg...) and some projects and sums should be in both(old and new). Now i am getting those projects only in "old".

Any idea how to solve it?

[rubenmarin1]

Hi, if you want one record with two possible values I think the best option is to use an additional table to avoid duplicated records.

So if you have a projects table with ProjectId as unique identifier of the project, you can add a related table like:

NewOld_Table:
LOAD
  ProjectId,
  'old' as New_Old
Resident Projects 
Where date>'31.03.2020' and date<'01.04.2021';
Concatenate
LOAD
  ProjectId,
  'new' as New_Old
Resident Projects 
Where date>'30.09.2020' and date<'01.10.2021';

[vinieme12]

flag the Old and New date ranges in your master calendar and then crosstab it

similar to this

https://community.qlik.com/t5/QlikView-Documents/Period-Presets-Compare-Periods-on-the-fly/ta-p/1486...