Hi, I am still trying to get my head around certain aspects of Qlikview so any help is appreciated.
I am trying to create a dashboard that includes information regarding accruals for several different customers. Due to the nature of accruals, there may be accruals present in one months data but then not present in the next months data and vice versa. There is a total of 12 reports for each month in a year and each report includes accruals up to and including the month of the report if they still exist.
Within the dashboard I have 1 sheet that does analysis of the data per month so I can look at the sum of accruals per month and display the information in different charts with different dimensions. I have another sheet where I am only curious at looking at the "most up to date" dataset. So for example if I was in the 4th month I would only be curious at looking at the 4th report as it includes the most recent and correct information. I was able to implement this into the expression of my charts using "[Closure Month]=Month4" and it works as intended.
The problem I have is that I want to update the dashboard each time there is a new report and currently I would have to edit the expression of each individual chart to achieve this. Obviously this is a long and tedious process and I was wondering if there would be a way to write the expression so that it automatically chooses the most recent report upon loading it into Qlikview? For example when I load in Report5 in the next month every chart would automatically update to the new report.
I am currently loading all the different reports which contain exactly the same fields from the same folder using:
Data: Field1, Field2, ... FROM [... Report*.xlsx]
This loads all the different reports I have, e.g. Report1, Report2, Report3, etc...
All help is appreciated and please do not be afraid if things are not clear. Due to the nature of the data I am unable to share any of my actual data as it contains customer information but I can always create an example dataset with false information if it helps for a better example.
If understand correctly, you want your "up to date" sheet to display data from the max month report. You could probably do that by looking at the max [Closure Month] but finding a max string can get quirky so you are better off using a numeric test.
If you don't already have a number that represents the [Closure Month], then generate by adding a field to your load statement:
mid([Closure Month], 6) as [Closure Month Num]
Then in your charts use a set that references the max month: