Calculation based on yes/no in load script

[dia2021]

Hi All.

I want to calculate the percentage based on the multiple (10) yes/no columns. I want to capture the aggregated percentage for the summary view based on all  'yes' columns data elements. 

I want to load all the calculated columns and values in the load script. What is the best possible way to achieve this? 

Please see attached file for your reference.

Any help is appreciated!

[JustinDallas]

It would be helpful if you posted what you expected your ending data to look like.

[dia2021]

Please see attached file for the end result and let me know if you have any questions. 

I need a rolled-up view in percentage format to show the completion of each process .

Thanks!

[Kushal_Chawda]

@dia2021  There is no "Yes" in D to L column in your sample data. How %completed calculated?

[dia2021]

It will eventually get populated in the dataset. I need to set up a script that refreshes % numbers automatically with the updated data. Basically, I need to capture avg of yes based on the whole dataset.

I know it can be done through the loop statement in the load script. I have attached the updated file for your reference.

Any help is greatly appreciated!

[rwunderlich · Accepted solution]

How about:

Data:
CrossTable(Process, Completed, 3)
LOAD Order,
     Product,
     Metric,
     E,
     F,
     G,
     H,
     I,
     J,
     K,
     L,
     M
FROM
Sample.xlsx
(ooxml, embedded labels, table is Sample);

Completed:
LOAD
*,
Num(YesProcessCount / AllProcessCount, '%0') as CompletedPct
;
LOAD
Order,
count(if(Completed = 'yes', Process)) as YesProcessCount,
count(Process) as AllProcessCount
Resident Data
Group By Order
;  

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[dia2021]

HI Rob, Thanks for your reply. 

How can I load the crosstab function without affecting the main table? Because other tables are getting affected when I apply this.  This is another requirement that I need to do on the same dashboard. 

Thanks for the idea. it worked without the crosstable function but I would like to know how crosstable (transpose table) can be achieved without affecting the main table?