Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Excel Calculations in Qlik Backend Script Help

Hi Experts,

Can any one please help me on below requirement.

In the below attached excel I have the Raw data from Column A to P. From these columns need to derive  Column P to U.

The calculations are included in the below excel.

Please help me to calculate the Columns from P to U in the backend only. The data is aggregated by Reporting Date, Date, Name and Folder.

Please find the below attached excel file and expected output.

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Hi,

Please looak at the attached qvf file.

G.

base:

Load * inline

[Reporting Date|Date|Name|Folder|Value|Item1|Item2|Item3|Item4|Item5|Item6|Item7|Item8|Item9|Item10|Item11

2018.09.11|2018.04.29|aim1|MAD|-13 697 056|0|0|0|5 589 217|13 623 768|13 623 768|12 565 927|12 565 927|||

2018.09.11|2017.04.22|aim1|MAD|58 207 976|10 948 770|15 046 180|25 461 408|36 004 973|51 909 039|80 770 242|94 078 344|111 917 257|||

2018.09.11|2018.04.29|aim1|LON|13 498 721|3 726 187|5 460 936|9 312 497|13 813 778|18 751 314|18 751 314|19 100 364|23 127 172|23 127 172|21 357 497|

2018.09.11|2018.03.06|aim1|LON|1 061 991|3 357 008|5 011 881|8 088 991|11 826 557|14 888 460|15 981 164|11 992 469|9 783 912|||

2018.09.11|2018.03.30|aim1|LON|-245 993|3 091 040|4 713 659|7 786 731|11 657 347|16 424 873|16 424 873|17 307 284|18 834 922|||

2018.09.11|2018.04.30|aim1|LON|-4 372 550|4 931 051|8 168 901|14 055 985|21 125 798|37 089 733|53 236 835|56 503 792|73 688 998|||

2018.09.11|2016.09.25|aim1|LON|29 392 098|8 342 825|11 551 983|19 089 361|26 891 764|34 460 667|57 473 772|65 559 470|76 979 238|||

2018.09.11|2012.06.05|aim1|LON|-8 217 008|1 670 430|7 023 089|15 171 330|26 166 680|35 825 013|57 777 584|71 250 167|99 168 824|99 168 824||

] (delimiter is '|');


Calcolumns_tmp:

Load

"Reporting Date",

    Name,

    Folder,

    if(Value<0,0,Value)+RangeMax(Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8,Item9,Item10,Item11)/5+0.1 as CalColumn1,

    -if(Value>0,0,Value)+RangeMax(Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8,Item9,Item10,Item11)/5+0.1 as CalColumn2   

Resident base;


Left join(Calcolumns_tmp)

Load

sum(CalColumn1) as CalColumn1_sum,

    sum(CalColumn2) as CalColumn2_sum

Resident Calcolumns_tmp;


Left join(Calcolumns_tmp)

Load

Folder,

    sum(CalColumn1) as CalColumn1_foldersum,

    sum(CalColumn2) as CalColumn2_foldersum

Resident Calcolumns_tmp

Group by Folder;


Calcolumns:

Load

RowNo() as RowNum,

"Reporting Date",

    Name,

    Folder,

    CalColumn1,

    CalColumn2,

    CalColumn1_foldersum/CalColumn1_sum as CalColumn1Percent,

    CalColumn2_foldersum/CalColumn2_sum as CalColumn2Percent  

Resident Calcolumns_tmp;


drop table base, Calcolumns_tmp;

View solution in original post

6 Replies
Highlighted
Creator III
Creator III

Try this with your data connection:

Temp:

CrossTable(Item, ValueItem,5)

LOAD

    "Reporting Date",

    "Date",

    Name,

    Folder,

    Value,

    Item1,

    Item2,

    Item3,

    Item4,

    Item5,

    Item6,

    Item7,

    Item8,

    Item9,

    Item10,

    Item11

    FROM [lib://Desktop (quynguyen_ducquy)/Source file.xlsx]

(ooxml, embedded labels, table is Sheet1);


CalTable:

Load *,

If(Value > 0, Value, 0) + ValueItemMax/5 + 0.1 As Cal1,

     If(Value < 0, Value*-1, 0) + ValueItemMax/5 + 0.1 As Cal2;

Load "Reporting Date",

    "Date",

    Name,

    Folder,

    Value,

    Max(ValueItem) As ValueItemMax

Resident Temp

Group by

"Reporting Date",    "Date",    Name,    Folder,    Value;

Drop Table Temp;


AggrTable:

Load "Reporting Date",

    Name,

    Folder,

    Sum(Cal1) As Cal1,

    Sum(Cal2) As Cal2

Resident CalTable

Group by "Reporting Date",    Name,    Folder;

Join

Load "Reporting Date",

    Name,

    Sum(Cal1) As Cal1_Total,

    Sum(Cal2) As Cal2_Total

Resident CalTable

Group by "Reporting Date",    Name;


Final_Data:

Load

"Reporting Date",

    Name,

    Folder,

    Cal1,

    Cal2

Resident CalTable;

Inner Join

Load "Reporting Date",

    Name,

    Folder,

    Cal1/Cal1_Total,

Cal2/Cal2_Total

Resident AggrTable;

Drop Table CalTable, AggrTable;

Highlighted
Specialist II
Specialist II

Hi,

Please looak at the attached qvf file.

G.

base:

Load * inline

[Reporting Date|Date|Name|Folder|Value|Item1|Item2|Item3|Item4|Item5|Item6|Item7|Item8|Item9|Item10|Item11

2018.09.11|2018.04.29|aim1|MAD|-13 697 056|0|0|0|5 589 217|13 623 768|13 623 768|12 565 927|12 565 927|||

2018.09.11|2017.04.22|aim1|MAD|58 207 976|10 948 770|15 046 180|25 461 408|36 004 973|51 909 039|80 770 242|94 078 344|111 917 257|||

2018.09.11|2018.04.29|aim1|LON|13 498 721|3 726 187|5 460 936|9 312 497|13 813 778|18 751 314|18 751 314|19 100 364|23 127 172|23 127 172|21 357 497|

2018.09.11|2018.03.06|aim1|LON|1 061 991|3 357 008|5 011 881|8 088 991|11 826 557|14 888 460|15 981 164|11 992 469|9 783 912|||

2018.09.11|2018.03.30|aim1|LON|-245 993|3 091 040|4 713 659|7 786 731|11 657 347|16 424 873|16 424 873|17 307 284|18 834 922|||

2018.09.11|2018.04.30|aim1|LON|-4 372 550|4 931 051|8 168 901|14 055 985|21 125 798|37 089 733|53 236 835|56 503 792|73 688 998|||

2018.09.11|2016.09.25|aim1|LON|29 392 098|8 342 825|11 551 983|19 089 361|26 891 764|34 460 667|57 473 772|65 559 470|76 979 238|||

2018.09.11|2012.06.05|aim1|LON|-8 217 008|1 670 430|7 023 089|15 171 330|26 166 680|35 825 013|57 777 584|71 250 167|99 168 824|99 168 824||

] (delimiter is '|');


Calcolumns_tmp:

Load

"Reporting Date",

    Name,

    Folder,

    if(Value<0,0,Value)+RangeMax(Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8,Item9,Item10,Item11)/5+0.1 as CalColumn1,

    -if(Value>0,0,Value)+RangeMax(Item1,Item2,Item3,Item4,Item5,Item6,Item7,Item8,Item9,Item10,Item11)/5+0.1 as CalColumn2   

Resident base;


Left join(Calcolumns_tmp)

Load

sum(CalColumn1) as CalColumn1_sum,

    sum(CalColumn2) as CalColumn2_sum

Resident Calcolumns_tmp;


Left join(Calcolumns_tmp)

Load

Folder,

    sum(CalColumn1) as CalColumn1_foldersum,

    sum(CalColumn2) as CalColumn2_foldersum

Resident Calcolumns_tmp

Group by Folder;


Calcolumns:

Load

RowNo() as RowNum,

"Reporting Date",

    Name,

    Folder,

    CalColumn1,

    CalColumn2,

    CalColumn1_foldersum/CalColumn1_sum as CalColumn1Percent,

    CalColumn2_foldersum/CalColumn2_sum as CalColumn2Percent  

Resident Calcolumns_tmp;


drop table base, Calcolumns_tmp;

View solution in original post

Highlighted
Specialist II
Specialist II

The sample application:

ExcelCalculations.qvf

Postscript: How can I upload file directly to the thread?

G.

Highlighted
Creator II
Creator II

Hi  undergrinder

Could you please attach the app in thread by removing in Forum.

You can upload the file by clicking on Use advanced editor.

use advanced editor.png

Attach.png

Highlighted
Specialist II
Specialist II

Thank you very much

Highlighted
Creator II
Creator II