Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
we have requirement as per below,
on 2nd April for Australia we have Invoice but budget is not there so "Expected MOnth End" is 0.
and China have Budget but no invoice is there so "Expected Month End" is 0 which is correct for both case.
But when we calculate for APAC means (Australia + China) then we as per below table for 2nd April we are getting Budget and Invoice both, so it is showing "Expected Month End" 6582230. we want it as 0 because Australia and China individual 0.
Date | (Australia) | (China) | |||||||||||
Invoice (USD) | Budget (USD) | Full Month Budget (USD) | Expected Month End | Invoice (USD) | Budget (USD) | Full Month Budget | Expected Month End | ||||||
1-Apr | 0 | 0 | 148,176 | 0 | 0 | 318 | 6,034 | 0 | |||||
2-Apr | 13573 | 0 | 148,176 | 0 | 0 | 318 | 6,034 | 0 | |||||
APAC | |||||||||||||
Invoice (USD) | Budget (USD) | Full Month Budget APAC(USD) | Expected Month End | ||||||||||
1-Apr | 0 | 318 | 154210 | 0 | |||||||||
2-Apr | 13573 | 318 | 154210 |
|
Expected Month end Formula is: ((full_month_budget * (100* invoiceMTD)/BudgetMTD)/100)
and We are having Country dimension.
Provide sample data or sample app for your this query..
Try Sum (Aggr(((full_month_budget * (100* invoiceMTD)/BudgetMTD)/100), Country))
Hello,
Thanks for your reply, I have tried with Aggr formula as you have provided but it is giving wrong result.
Well, if you supply a QlikView document with some example data showing the problem would be helpful. I tried to make a simple data model to show the problem and a solution, but the data you have given is insufficent to make an example. Preferrable with some inline data loads.
/Johan