topic Re: The calculated string in the dimension in QlikView

The calculated string in the dimension

kolov007 — Thu, 04 Jan 2018 09:18:19 GMT

Hello.

I have a table with metrics. I need to calculate value for the certain line (Percent of Profit) by certain formula, and for the all other values by another sum([Amount of USD]).

I have a formula:

if(Metrics = 'Percent of Profit', Sum ({<Metrics = {'Sales'}>}[Amount of USD])/Sum ({{<Metrics = {'Cost'}>}[Amount of USD])-1,

[Amount of USD]) but this formula is invalid.

Please help me with this task.

Re: The calculated string in the dimension

Anil_Babu_Samineni — Thu, 04 Jan 2018 09:29:35 GMT

I don't have access to check. But, My assumption is

Sum(Aggr(if(Metrics = 'Percent of Profit', Sum ({<Metrics = {'Sales'}>}[Amount of USD])/Sum ({{<Metrics = {'Cost'}>}[Amount of USD])-1, [Amount of USD]), Metrics))

Re: The calculated string in the dimension

tresB — Thu, 04 Jan 2018 09:30:44 GMT

Like this?

Re: The calculated string in the dimension

kolov007 — Thu, 04 Jan 2018 12:27:00 GMT

It's not work

Re: The calculated string in the dimension

sunny_talwar — Thu, 04 Jan 2018 12:49:18 GMT

Try this

if(Metrics = 'Percent of Profit', Num(Sum(TOTAL {<Metrics = {'Sales'}>}[Amount of USD])/Sum(TOTAL {<Metrics = {'Cost'}>}[Amount of USD])-1, '#,##%'), Num([Amount of USD], '#,00'))