Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
infosense_devel
Creator II
Creator II

Group by Country in Expression.

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
for VAUS

Invoice (USD)

Budget (USD)

Full Month Budget

Expected Month End
for VCHN

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)
(VAUS+VCHN)

Budget (USD)
(VAUS+VCHN)

Full Month Budget APAC(USD)

Expected Month End
for APAC

1-Apr

0

318

154210

0

2-Apr

13573

318

154210

  1. 6582290.823

Expected Month end Formula is:     ((full_month_budget * (100* invoiceMTD)/BudgetMTD)/100)


and We are having Country dimension.

4 Replies
MK_QSL
MVP
MVP

Provide sample data or sample app for your this query..

johanlindell
Partner
Partner

Try Sum (Aggr(((full_month_budget * (100* invoiceMTD)/BudgetMTD)/100), Country))

infosense_devel
Creator II
Creator II
Author

Hello,

Thanks for your reply, I have tried with Aggr formula as you have provided but it is giving wrong result.

johanlindell
Partner
Partner

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