Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
posywang
Creator
Creator

Data not show unless Always Fully Expanded and another question

Hi, we are trying to design a report to compare resource demand and capacity from two different data source. In the attached example file, you can see that two tables have common dimensions but also have its own unique dimensions. For records that have same combination of Resource, ResourceType, Year, and Mth, I used ApplyMap to add the missing unique dimensions to data from the other table, and concatenate both tables at the end. By doing it, I identified two issues and would like to have your help.

1. The pivot table won't show any data unless Always Fully Expanded option is selected. However, I'd like to have a regular pivot table that I can collapse and expand dimensions. Can this be fixed?

2. In some cases, resource is allocated to projects for longer period than their capacity. You can see that John is allocated to projects for 12 months, but his capacity is only until June. We'd like to apply the following rule to the data but I don't know how:

- If value is null() of a month, get a value from the most recent month that has a value. For example, Location of December in Demand row is null, logic should check the previous months and get location information of NY from June sine June is the closest month to December that has a value.

Your help is greatly appreciated! Thank you!

1 Reply
JM
Contributor III
Contributor III

I think you need to fix the all missing values for your Resource, time, location key. I think ApplyMAP usually not the right stragtegy to do this, because its a key, value replacement, doesn't provide anything else other than substituting values.

I try to solve your problem, in the attached file.

Cheers,

J.M