Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
manpri7078
Creator
Creator

YTD Expression similar to SUMPRODUCT in EXCEL

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

1 Solution

Accepted Solutions
sunny_talwar

12 Replies
sunny_talwar

Check attached

Capture.PNG

manpri7078
Creator
Creator
Author

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
Creator
Creator
Author

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

Check attached

Capture.PNG

manpri7078
Creator
Creator
Author

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
Creator
Creator
Author

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?

Capture.PNG

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
Creator
Creator
Author

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