topic Complex Requirement in excel in QlikView

Complex Requirement in excel

Sat, 14 Nov 2015 18:29:34 GMT

Hi swuehl‌ - Stefan,

Appreciate all the help in the past. I have a very complex requirement on which I'm working. Would really appreciate your help.

I'm attaching a few excel files which are a part of my data source.

I want a bar chart in such a way that when a user selects a month, say Sep-15

So basically the chart should have three Sep month's from 2015Aug file, 2015July file and 2015Sep file on x axis and corresponding forecast values should be calculated based on metric selection.

Note: I have many more excel files and new ones coming every month. I have deleted a few metrics from the attached excel.

Would really appreciate if you could send me a sample qlik view code for the mentioned requirement.

Regards,

Sachin

Re: Complex Requirement in excel

Kushal_Chawda — Sat, 14 Nov 2015 19:45:48 GMT

Data:

CrossTable(MONTH, Data, 3)

LOAD Metric,

FileBaseName() as FileName,

Country,

[42005],

[42036],

[42064],

[42095],

[42125],

[42156],

[42186],

[42217],

[42248],

[42278],

[42309],

[42339]

FROM

[2015*.xlsx]

(ooxml, embedded labels, table is Data);

New:

noconcatenate

LOAD *,

Year(trim(MONTH)) as Year,

month(trim(MONTH)) as Month,

monthname(trim(MONTH)) as MonthYear

Resident Data;

DROP Field MONTH;

DROP Table Data;

Note : Sheet Name of all excel file should be same, then only you can load all the excel file using * prefix else if sheet of all excel file is not same then you need to perform loop to load the file for which follow below link

https://community.qlik.com/docs/DOC-7860‌

Re: Complex Requirement in excel

Mon, 16 Nov 2015 15:21:15 GMT

Hi Kush,

Appreciate your help on the solution. There is just one small change can you help me with that in the solution. When the user selects July, only data for July from July file should be displayed because it doesn’t make business sense to show data from future files.

Whereas when user selects Aug it should show Aug data from July file as well as from Aug file but not from Sep file.

And lastly when a user selects Sep it should show data for Sep from all the three files.

Attached is a sample screen shot of expected solution.

Thank you for all the help.

Regards,

Sachin