topic Re: expression total is null (-) in QlikView

expression total is null (-)

achates2008 — Tue, 22 Jan 2019 08:16:19 GMT

I has been stuck in this issue for long time. Please someone help me out!

I have a straight table which will calculate sales and convert it to USD with "current" FX rate based on selection. I have a dimension "country" which has multiple values and each has a currency of the country.

In below expression, If I select one country, everything is fine, total sales is also correct because the rate will be unique. However, if don't select the country, the total will be null. I know the problem is the calculation of the rate, but since the rate should be based on user selection, how can I avoid the issue? (SO# is sales order number, I tried to calculate from the lowest level, but looks like doesn't work).

I can't force user to select a country because this is a summary report, every country should be shown in one chart.

sum(Sales) * aggr(Only({<BL_Period={$(=max(period))}>}BL_rate),SO#)

Re: expression total is null (-)

anushree1 — Tue, 22 Jan 2019 10:51:36 GMT

Hi,

You could probably use a work around here ,that in case a country is not Selected the Total appears as NA, and in case the country is selected , it shows the total , as said when a country is not selected you would not know the rate conversion to be used.

Please check the attachment wherein i Have used Dimensionality which states the number of Dimensions used , in the sample I have used 2 Dimensions and thus the Dimensionality () is 2.

Hope this helps

Re: expression total is null (-)

achates2008 — Tue, 22 Jan 2019 12:24:38 GMT

Hi Anushree,

Thank you for your answer, but the total is required by user.

The actual requirements are even more complex. It will have country-city structure, and need to show sub total per country. So I need to change the chart to pivot table later. Anyway, the logic should be same.

There is another similar case as mine, but don't know how to migrate it to my case. Any idea?

https://community.qlik.com/t5/QlikView-App-Development/Sum-of-rows-in-pivot-table/m-p/460582

Re: expression total is null (-)

anushree1 — Tue, 22 Jan 2019 12:35:52 GMT

Well in that case why not use conditional enabling and show the pivot chart when getselectedcount(city)>0 and use the dimensionality on pivot table , Dimensionality for total on Pivot is zero.
Furthermore , if the total needs to be displayed even when no country is selected what is the rate you are supposed to assume?
In case you need further changes , I would say its better you share the sample copy of the application as working without the data in hand gets difficult to understand the actual requirement

Re: expression total is null (-)

sunny_talwar — Tue, 22 Jan 2019 12:57:25 GMT

How about this

Sum(Aggr(
Sum(Sales) * Only({<BL_Period={$(=Max(period))}>} BL_rate)
, [SO#], Country))

Re: expression total is null (-)

achates2008 — Tue, 22 Jan 2019 12:57:43 GMT

Actually it should be always fully expanded, no any conditional needed.

The rate is per SO level, so sum(Sales*rate) has no problem. As mentioned, different country has different rate, so on total level, there isn't a rate. The total must be roll up from row level, but must use expression total in pivot table.

You are right, a real example is the best. I would provide a screenshot as below at first to help on understanding.

Re: expression total is null (-)

achates2008 — Tue, 22 Jan 2019 13:09:54 GMT

Fantastic! This is exactly the answer.
Thank you so much, Sunny!