YTD Expression similar to SUMPRODUCT in EXCEL

[manpri7078]

Hi All,

I am struck in one place. I have a new requirement to calculate YTD Average of some data for which I am attaching sample file.

In my sample file P&B Qty or Actual Qty have figures where Particulars is Metal.

Another line Item with the name of Alumina have other data such as effeciency, issue prices of both P&B and Actual.

I have given there my desired output which is calculated through EXCEL's very popular SUMPRODUCT formula. That desired output I want in my Sample Qlikview Document, which also I am attaching herewith.

I have made a simple Pivot chart for the same. Can any one please help how to achieve the results which can be easily done by SUMPRODUCT formula, but I have not found anything similar to that in Qlikview.

I hope I have been clear in explaining my problem and what I actually want.

Regards,

Manish Prasad

[sunny_talwar]

Check attached

[manpri7078]

Dear Sunny,

Have gone through your expression. The Result matches as per my query here. But I am making pivot chart and there are more than one item head besides Alumina. Your expression for Actual Issue Price or P&B Issue Price is not working as desired.

Otherwise for single item Alumina in my sample document, it is matching.

I am once again uploading my sample document and also sample Excel file with more line items as per Actual file.

Please explore the table as is now appearing. I have not used Aggr function before so I am not aware of the technical problem here. But as you can see that the third and forth expressions are not displaying as desired.

Regards,

Manish Prasad

[sunny_talwar]

Share the expected output for this new sample that you have shared....

[manpri7078]

I have tried to show my desired output in the second sheet of the sample excel file. I am also once again sharing both EXCEL and QLIKVIEW document.

Basically what you have done earlier was the case of one item, Alumina.

Now I have several other items besides Alumina which may be n number of items. Please explore my desired output. It is calculated on the same lines as was done for Alumina. For example instead of Alumina if earlier only Anode would have been there then your expressions should have shown correct output in all columns

Now I want this result to be there in my final Pivot Chart.

I hope I have been able to clarify my exact problem.

Regards,

Manish Prasad

[sunny_talwar · Accepted solution]

Check attached

[manpri7078]

Dear Sunny,

Well Done. Now I understands the logic behind your expressions. It will be helpful now to make my final expressions which are based on your expressions.

Regards

Manish Prasad

[manpri7078]

Dear  Sunny,

One more little help required. I am attaching my document once again but now with my final pivot table also there as can be seen at the left bottom of my document.

I am also making two text boxes. In it I am trying to show the total of column 1 and column 2 of the pivot chart respectively.

My pivot chart figures are displaying as desired. I have used same expressions in my text objects in my sample document. I know I need to modify the text object expressions so as to show the total of all rows. But I am unable to do so. Something I am missing.

Can you please modify my text box expressions to show the correct figures tallying with the total of the rows of the pivot chart.

Regards

Manish Prasad

[sunny_talwar]

Are these the numbers you were looking to get?

It is a simple concept, you since you need a sum of rows, you can simply wrap your expression around with Sum(Aggr()) function like this:

Sum(Aggr(YourExpression, Particulars))

Here Particulars was your chart dimension.....

Also, look here

Totals in Charts‌

Sum of rows in pivot tables ‒ QlikView‌ -> this is in pivot table, but follows the same logic because there is no total mode present in pivot table like straight table.

[manpri7078]

Dear Sunny,

Thanks for your suggestion. It really rocks. Such a simple concept I was missing.

Now I am supposed to learn the various uses of AGGR.

Can you please post here some good links for learning AGGR. I will also meanwhile search for it.

Regards

Manish Prasad