Apply different filters for one field in the same pivot table

[MiaLii]

Hi All

We currently have the database with columns:

Job Number

ETD

ETA

Direction

Each job has its own direction as Import/Export/Cross trade, and we would like to measure Import by ETA and Export by ETD.

We would like to show the volume for Import by ETA +Export by ETD each month in a pivot table, Direction under the Dimensions, and the Volume Sum under Measures. 

Could you please help with the expression or statement?

Thanks in advance. 

[Rohan]

Hi MiaLii,

It seems your issue is with applying the same month filter on 2 date fields ie ETA & ETD, There are 2 approaches to achieve this. You can either create a Cannonical  Calender with date bridge or an Island Calendar to handle this.

I would suggest to go with an Island Calendar approach. You can refer below links & implement the one you think is easy for you. 

Cannonical Calendar : 

https://community.qlik.com/t5/Design/Canonical-Date/ba-p/1463578

Island Calendar :

https://cassio-bolba.medium.com/date-island-qlik-sense-25ea793aca2d

Revert if you face any issues.

Regards,

Rohan.

[Anil_Babu_Samineni]

@MiaLii Try this

Dimension: MonthField

Expression: Sum({<Direction={'Import'}>}ETA)+Sum({<Direction={'Export'}>}ETD)

[MiaLii]

Hi @Rohan 

Thanks for your reply. 

Just to double confirm if my understanding is correct. 

The basic thing of using Island Calendar is to:

1, Create an islanded DATA field, make sure it includes all the possible Date.

2, Using IF condition to build the relationship between the DATA field and the final result.

3, Create a filter panel wit the DATA field.

4, If there is no selection in the DATA field Filter panel, then the SUM will be the total in the database for that dimension. If we choose MonthXX then the sum will be that month's.

If we have the above data, and something like the below in a Table chart:

Sum(if([ETD] =Data and [DIRECTION]= 'Export',  [VOLUME] ))
Sum(if([ETA] =Data and [DIRECTION]= 'Import',  [VOLUME] ))

then select Feb, the result will be only the volume for Job# 1002 and 1003. 

Please kindly let me know if this is correct.

Thanks a lot. 

[Rohan]

Hi @MiaLii ,

Once you have created the Island Calendar, you will use the Island Fields in the filter panes in the dashboard. For this specific case of table use the following measure :

Sum({<ETA={">=$(=date(monthstart(max(Island_Date)))) <=$(=date(max(Island_Date)))"},Direction={"Import"}>}Volume) + Sum({<ETD={">=$(=date(monthstart(max(Island_Date)))) <=$(=date(max(Island_Date)))"},Direction={"Export"}>}Volume)

Try it out & revert if any issues. I have added restrictions for MTD data in the set expression. so basically you can change that to min(island_date) & max(island_date) as per your convenience.

Regards,

Rohan.