topic Generate formulas in table in QlikView App Dev
https://community.qlik.com/t5/QlikView-App-Dev/Generate-formulas-in-table/m-p/1585094#M456685
<P>Hello,<BR />I try to create an overview of key figures in a table. This could be a pivot table, for example. I still need two comment fields for the key figure. This table can look like this:</P><TABLE border="1"><TBODY><TR><TD>KPI</TD><TD>Comment1</TD><TD>Comment 2</TD><TD>Formula</TD></TR><TR><TD>turnover</TD><TD>a</TD><TD>a</TD><TD>2.015.200</TD></TR><TR><TD>ROI</TD><TD>s</TD><TD>s</TD><TD>215.000</TD></TR><TR><TD>Sum of 5+5</TD><TD>d</TD><TD>d</TD><TD>10</TD></TR><TR><TD>Sum of 4+4</TD><TD>f</TD><TD>f</TD><TD>8</TD></TR></TBODY></TABLE><P>The Formula column is to be calculated from different QlikView fields with different formulas. Does anyone know a good solution for this?</P><P>My idea is to load a new table for key figures into the data model. I create the new table in an Excel spreadsheet. In this table I define all columns and write the formulas I need. This will look like this.</P><TABLE border="1"><TBODY><TR><TD>KPI</TD><TD>Comment1</TD><TD>Comment 2</TD><TD>Formula</TD></TR><TR><TD>turnover</TD><TD>a</TD><TD>a</TD><TD>=sum(turnover)</TD></TR><TR><TD>ROI</TD><TD>s</TD><TD>s</TD><TD>=sum(NetIncome)/sum(Investment)</TD></TR><TR><TD>Sum of 5+5</TD><TD>d</TD><TD>d</TD><TD>=5+5</TD></TR><TR><TD>Sum of 4+4</TD><TD>f</TD><TD>f</TD><TD>=4+4</TD></TR></TBODY></TABLE><P><BR />Now I have created the table in QlikView. For this I created a pivot with the columns KPI, Comment1 and Comment2. As formula I use the following function:</P><P><EM>$(=Formula)</EM></P><P>If I filter the table to one KPI it works. With several formulas this doesn't work anymore. Does anyone have an idea what I did wrong?</P><P>I hope someone of you can help me.<BR />Greetings<BR />Alex</P><P> </P><P> </P><P> </P><P> </P>Mon, 27 May 2019 13:02:22 GMTqlikviewalex2019-05-27T13:02:22ZGenerate formulas in table
https://community.qlik.com/t5/QlikView-App-Dev/Generate-formulas-in-table/m-p/1585094#M456685
<P>Hello,<BR />I try to create an overview of key figures in a table. This could be a pivot table, for example. I still need two comment fields for the key figure. This table can look like this:</P><TABLE border="1"><TBODY><TR><TD>KPI</TD><TD>Comment1</TD><TD>Comment 2</TD><TD>Formula</TD></TR><TR><TD>turnover</TD><TD>a</TD><TD>a</TD><TD>2.015.200</TD></TR><TR><TD>ROI</TD><TD>s</TD><TD>s</TD><TD>215.000</TD></TR><TR><TD>Sum of 5+5</TD><TD>d</TD><TD>d</TD><TD>10</TD></TR><TR><TD>Sum of 4+4</TD><TD>f</TD><TD>f</TD><TD>8</TD></TR></TBODY></TABLE><P>The Formula column is to be calculated from different QlikView fields with different formulas. Does anyone know a good solution for this?</P><P>My idea is to load a new table for key figures into the data model. I create the new table in an Excel spreadsheet. In this table I define all columns and write the formulas I need. This will look like this.</P><TABLE border="1"><TBODY><TR><TD>KPI</TD><TD>Comment1</TD><TD>Comment 2</TD><TD>Formula</TD></TR><TR><TD>turnover</TD><TD>a</TD><TD>a</TD><TD>=sum(turnover)</TD></TR><TR><TD>ROI</TD><TD>s</TD><TD>s</TD><TD>=sum(NetIncome)/sum(Investment)</TD></TR><TR><TD>Sum of 5+5</TD><TD>d</TD><TD>d</TD><TD>=5+5</TD></TR><TR><TD>Sum of 4+4</TD><TD>f</TD><TD>f</TD><TD>=4+4</TD></TR></TBODY></TABLE><P><BR />Now I have created the table in QlikView. For this I created a pivot with the columns KPI, Comment1 and Comment2. As formula I use the following function:</P><P><EM>$(=Formula)</EM></P><P>If I filter the table to one KPI it works. With several formulas this doesn't work anymore. Does anyone have an idea what I did wrong?</P><P>I hope someone of you can help me.<BR />Greetings<BR />Alex</P><P> </P><P> </P><P> </P><P> </P>Mon, 27 May 2019 13:02:22 GMThttps://community.qlik.com/t5/QlikView-App-Dev/Generate-formulas-in-table/m-p/1585094#M456685qlikviewalex2019-05-27T13:02:22ZRe: Generate formulas in table
https://community.qlik.com/t5/QlikView-App-Dev/Generate-formulas-in-table/m-p/1585221#M456686
<P>Hi,<BR /><BR />You'd have to get a calculated dimension with ValueList like so:<BR /><BR />=ValueList('Turnover','ROI','Sum of 5+5','Sum of 4+4')<BR /><BR />This will create a dummy dimension with 4 text on it.<BR />As for expression, you'd use:<BR /><BR />pick<BR />(<BR />match<BR />(<BR />ValueList('Turnover','ROI','Sum of 5+5','Sum of 4+4'),<BR />'Turnover',<BR />'ROI',<BR />'Sum of 5+5',<BR />'Sum of 4+4'<BR />),<BR />$(v1),<BR />$(v2),<BR />$(v3),<BR />$(v4)<BR />)<BR /><BR />Ive created the v1,v2,v3 and v4 variables like so:<BR />v1: sum(value)<BR />v2: 2<BR />v3: 5+5<BR />v4: 4+4<BR /><BR /></P><P> </P><P>Resulting in:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="sample.png" style="width: 316px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/12735iB60CE2CBA9E155B7/image-size/large?v=v2&px=999" role="button" title="sample.png" alt="sample.png" /></span></P>Mon, 27 May 2019 19:04:42 GMThttps://community.qlik.com/t5/QlikView-App-Dev/Generate-formulas-in-table/m-p/1585221#M456686felipedl2019-05-27T19:04:42Z