Now the next challenge is that this only data for 1 month.
So each month has its own excel file.
I would like the report to search all excel files in the directory (they have a naming convention: YEAR_MONTH.xlsx) and show me a Bar Chart with 2 dimensions: YEAR_MONTH on x axis, and Sum(Amount) (based on Raw Data would be 234 for this example) on y axis.
I would like also every time I copy a new file with a new month to the report directory, the chart auto updates with a new month for infinite months.
Last but not the least I would like to click on a bar corresponding to a month a automatically show the corresponding Pivot Report table for that month.
AS Vivien said, if you only have that type of file in your directory you can use the wildcards proposed.
As for the automatic pivot table when clicking on a bar, that's not a default behavior so the only way to achieve that without getting into macros, because I don't use them and don't know how to code them, would be to enable that type of chart in the Fast Type Change option. So.. once the user has selected a bar, it'll have to click on that icon to see the pivot table representation.
Perhaps you could simulate that kind of behavior using the show conditional option in the ( Object Properties: Layout tab ), So, you'll have two objects, the bar chart and the pivot table.
The bar chart would have the next expression:
GetPossibleCount(YEAR_MONTH) > 1
While the pivot table the opposite:
GetPossibleCount(YEAR_MONTH) = 1
Whenever the user selects only one YEAR_MONTH, the pivot table will be shown, otherwise the bar chart will be visible.
do not forget that I just want to create a pivot table in my qlikview report and I don't want to create a new qlikview report table each time a new month is added. So I didn't understand your help, but thanks anyway.
var fileArray = GetFilesFromDir();
foreach(file in fileArray)
var yearMonth = GetFromFile(file);
var currentTable = LOAD yearMonth as YEAR_MONTH, * FROM file;
For the bar chart:
Use column YEAR_MONTH as 1st dimension. So when I click on a month it will filter the pivot table automatically.