topic Total and Count IF of the Sum Result in QlikView App Development
https://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422323#M157361
<HTML><HEAD></HEAD><BODY><P>Hi all,</P><P>I've been strugling over this TOTAL and COUNT IF problem. It should've been easy, but i just have no idea yet.</P><P>I've three pre-loaded field: Product, Annual Cost and Actual Cost.</P><P>Then in straigt-table i've to put Budget Cost, which have value range based on Annual Cost, like if the product have Annual Cost less than 900M, the Budget Cost value should 50M.</P><P></P><P>Budget Cost expression is as follows </P><P><EM>=if((Sum([Annual Cost]))>1 and (Sum([Annual Cost]))<900000000, 50000000, </EM></P><P><EM>if((Sum([Annual Cost]))>900000000 and (Sum([Annual Cost]))<2400000000, 100000000, </EM></P><P><EM>if((Sum([Annual Cost]))>2400000000 and (Sum([Annual Cost]))<6000000000, 200000000, </EM></P><P><EM>if((Sum([Annual Cost]))>6000000000, 500000000</EM></P><P><EM>))))</EM></P><P></P><P>And then i've to put [% Budget vs Actual], which the expression should be Actual/Budget. It's OK on the field, but not as i expect in TOTAL. Which lead incorrect percentage in TOTAL [% Budget vs Actual Cost]. See pic. </P><P></P><P>I expect the TOTAL would result the sum of the new-created Budget Cost.</P><P></P><P><IMG __jive_id="41817" class="jive-image-thumbnail jive-image jiveImage" alt="6.jpg" src="https://community.qlik.com/legacyfs/online/41817_6.jpg" width="450" /></P><P></P><P>Then i have to put text field to show count ( if the [% Budget vs Actual] > 100% ). i've tried set analysis, and add variable (Actual/Budget), and write expression as follow:</P><P><EM>=count({$<v1={'>1'}>} Product)</EM></P><P></P><P>Is it possible? Thanks in advance.<EM><BR /></EM></P></BODY></HTML>Thu, 30 May 2013 05:11:16 GMT2013-05-30T05:11:16ZTotal and Count IF of the Sum Result
https://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422323#M157361
<HTML><HEAD></HEAD><BODY><P>Hi all,</P><P>I've been strugling over this TOTAL and COUNT IF problem. It should've been easy, but i just have no idea yet.</P><P>I've three pre-loaded field: Product, Annual Cost and Actual Cost.</P><P>Then in straigt-table i've to put Budget Cost, which have value range based on Annual Cost, like if the product have Annual Cost less than 900M, the Budget Cost value should 50M.</P><P></P><P>Budget Cost expression is as follows </P><P><EM>=if((Sum([Annual Cost]))>1 and (Sum([Annual Cost]))<900000000, 50000000, </EM></P><P><EM>if((Sum([Annual Cost]))>900000000 and (Sum([Annual Cost]))<2400000000, 100000000, </EM></P><P><EM>if((Sum([Annual Cost]))>2400000000 and (Sum([Annual Cost]))<6000000000, 200000000, </EM></P><P><EM>if((Sum([Annual Cost]))>6000000000, 500000000</EM></P><P><EM>))))</EM></P><P></P><P>And then i've to put [% Budget vs Actual], which the expression should be Actual/Budget. It's OK on the field, but not as i expect in TOTAL. Which lead incorrect percentage in TOTAL [% Budget vs Actual Cost]. See pic. </P><P></P><P>I expect the TOTAL would result the sum of the new-created Budget Cost.</P><P></P><P><IMG __jive_id="41817" class="jive-image-thumbnail jive-image jiveImage" alt="6.jpg" src="https://community.qlik.com/legacyfs/online/41817_6.jpg" width="450" /></P><P></P><P>Then i have to put text field to show count ( if the [% Budget vs Actual] > 100% ). i've tried set analysis, and add variable (Actual/Budget), and write expression as follow:</P><P><EM>=count({$<v1={'>1'}>} Product)</EM></P><P></P><P>Is it possible? Thanks in advance.<EM><BR /></EM></P></BODY></HTML>Thu, 30 May 2013 05:11:16 GMThttps://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422323#M1573612013-05-30T05:11:16ZRe: Total and Count IF of the Sum Result
https://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422324#M157362
<HTML><HEAD></HEAD><BODY><P>In your % actual buget write expression and check on relative </P><P>See the link also</P><P><A _jive_internal="true" class="loading" href="https://community.qlik.com/thread/56813">http://community.qlik.com/thread/56813</A></P><P>hope it helps</P></BODY></HTML>Thu, 30 May 2013 06:06:43 GMThttps://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422324#M157362er_mohit2013-05-30T06:06:43ZRe: Total and Count IF of the Sum Result
https://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422325#M157363
<HTML><HEAD></HEAD><BODY><P>my expression for %actual/budget is</P><P><EM>=sum([Actual cost])/(if((Sum([Annual Cost]))>1 and (Sum([Annual Cost]))<900000000, 50000000, </EM></P><P><EM>if((Sum([Annual Cost]))>900000000 and (Sum([Annual Cost]))<2400000000, 100000000, </EM></P><P><EM>if((Sum([Annual Cost]))>2400000000 and (Sum([Annual Cost]))<6000000000, 200000000, </EM></P><P><EM>if((Sum([Annual Cost]))>6000000000, 500000000</EM></P><P><EM>)))))</EM></P><P></P><P>in which per rows, give me correct answer. the incorrect TOTAL on %a/b is because the incorrect TOTAL of 'Budget Cost'. the 'Budget Cost' TOTAL is not summing the rows, but reflect the if's (the TOTAL is <EM>500M </EM>because the 'actual' sum total is more than 6000M)</P><P></P><P>i tried 'relatif' option, but the answer is not yet my expectation.</P><P></P><P>thank you for the answer</P></BODY></HTML>Thu, 30 May 2013 08:01:33 GMThttps://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422325#M1573632013-05-30T08:01:33ZRe: Total and Count IF of the Sum Result
https://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422326#M157364
<HTML><HEAD></HEAD><BODY><P>oh i forgot to mention that i expect, the TOTAL Budget Cost is (if correct sum, 5500M), so that the TOTAL [% Actual/Budget] would be 58.50%. </P><P></P><P>And for the text, COUNT if [% Actual/Budget] more than 100%, it should be 3 product.</P><P>Any idea? Thank you</P></BODY></HTML>Thu, 30 May 2013 08:10:19 GMThttps://community.qlik.com/t5/QlikView-App-Development/Total-and-Count-IF-of-the-Sum-Result/m-p/422326#M1573642013-05-30T08:10:19Z