Filtering by measures

[Joo525]

Hi,

I have problem with my App.

As I asked bevor, I have a table with the current condition consisting of start date, end date and the price during that period, and the previous condition with the same values (start date, end date, price). So all together 6 rows. I have created measures (as master elements) with max(), max (,2) for the dates and firstSortedValue()  gives me the according price.

Now I need to filter by max(start date). I doesn't work because max(start date) is a measure. Is there any trick or other possibility to filter that way?

 

Thank you for your help. 

[mato32188]

Hi @Joo525 ,

I miss a bit how does your data model look like. Perhaps few suggestions from my point of view. I would do the concatenation of your previous and actual data snapshots and create few flags to satisfy your need.

Script editor.

tmpFACT:

Load

date(floor([Startdatum aktuelle Kondition])) as [Start Date],

date(floor([Enddatum aktuelle Kondition])) as [End Date],

[aktueller Preis je VE] as Price,

'Actual' as Period,

FROM YourTableWithActualData;

concatenate(tmpFACT)

Load

date(floor([Startdatum vorherige Kondition])) as [Start Date],

date(floor([Enddatum vorherige Kondition])) as [End Date],

[vorherige Preis je VE] as Price,

'Previous' as Period,

FROM YourTableWithPreviousData;

MAP_MAX_START_DATE_ACTUAL:

Mapping Load

Max(floor([Start Date])),

1

Resident tmpFACT

where Period = 'Actual';

MAP_MAX_START_DATE_PREVIOUS:

Mapping Load

Max(floor([Start Date])),

1

Resident tmpFACT

where Period = 'Previous';

FACT:

Load

*,

ApplyMap('MAP_MAX_START_DATE_ACTUAL',floor([Start Date]),null()) as [Flag Max Start Date Actual],

ApplyMap('MAP_MAX_START_DATE_PREVIOUS',floor([Start Date]),null()) as [Flag Max Start Date Previous]

Resident tmpFACT;

drop table tmpFACT;

----------------------------------------------------------------------------------------------------

Your expression can look like:

sum( { < [Flag Max Start Date Actual] = {1} > } Price)

sum( { < [Flag Max Start Date Previous] = {1} > } Price)

It is always preferred option to create such "pointers" in data and then use them simply in expressions.

Best regards,

m

[Joo525]

Here is my data model:

I load the data from an external database looking like the following example:

 I want to have the following result:

 I am working with the editor (have all together 5 different tables where I get even more information from) but when I programm yout solution as you showed do I need a different sheet?

[mato32188]

@Joo525 ,

right, it brings more light into it. Sending you .qvf file where you can find the output.

Br

m

[Joo525]

This solutions looks much better than my one. I think I will re-programm it.

But the filtering still doesn't work the way I need. When I filter for the 01.01.2021, Qlik is suppost to show me the whole row with data from all articles that have start date 01.01.2021 (start/end date actual and previous condition with both prices).

[mato32188]

Perhaps I've got your point.

Try to add few rows into your script editor (in BOLD ).

FACT:
LOAD
article,
"date from",
"date to",
price,
Coalesce(Period1,Period2) as Period
Resident tmp1;
drop table tmp1;

//----------------------------------

left join (FACT)
Load
article,
"date from" as StartDate1,
'Previous' as Period
Resident FACT
where Period = 'Actual';

left join (FACT)
Load
article,
"date from" as StartDate2,
'Actual' as Period
Resident FACT
where Period = 'Actual';

FINAL:
LOAD
article,
"date from",
"date to",
price,
Period,
Coalesce(StartDate1,StartDate2) as StartDate
Resident FACT;
drop table FACT;

Now you can filter by StartDate to have only one row for 01.01.2021 in pivot table.

BR

m

[mato32188]

Hi @Joo525 ,

does it satisfy your need?

Thanks.