topic How to sum up values using If when the value is splitted into two rows in QlikView

How to sum up values using If when the value is splitted into two rows

Nishanthi — Fri, 15 Nov 2024 23:11:45 GMT

In the below scenario, if Jan month has fcst and actual , then only "Actual" has to be considered. If there's no entry for actual then "Fcst" has to be considered.

Sample data:

Project id	Month	Budget Type	Amount
123	Jan	Fcst	10,000
123	Jan	Actual	5,000
999	Jan	Fcst	12,000
658	Jan	Fcst	30,000
658	Jan	Actual	30,000

Re: How to sum up values using If when the value is splitted into two rows

edwin — Thu, 04 Feb 2021 13:50:29 GMT

a simple expression would be:
if(count({<[Budget Type]={'Actual'}>}[Budget Type])>0, sum({<[Budget Type]={'Actual'}>}Amount), sum({<[Budget Type]={'Fcst'}>}Amount))

which translates to if there is at least 1 count of Budget type=actual for the product, use actual else use forecast
this assumes that you will use Product at least as dimension

Re: How to sum up values using If when the value is splitted into two rows

Nishanthi — Thu, 04 Feb 2021 15:02:27 GMT

Thanks Edwin. But any chance to pull in the backend ?