How to mix aggr() and if statements correctly - price effect calculation

[#1]

I need to find the correct function to obtain the attached results.

In red you will find the expected results.

To make a long story short, I need to find the to eliminate the rows in which ther's not a qty, sales or prices for both the analysed period and calculate correctly the price effect and the related %.

Thanks in advance for your support!!

Mb

[sunny_talwar]

Will you be able to share the qvw where you have loaded this sample data into the application?

Best,

Sunny

[#3]

‌ciao Sunny

The original database is too huge.

In any case, you can use the column

Customer

SKu

Qty

Sales

price

For 2014 and 2015

the other columns are only used for the next explanation  of the problem.

Thanks

In advance

Ciao

[sunny_talwar]

Is this what you want?

I was not able to get the headers right for the last three columns, but you can place a text box on top of them.

Check it out in the attached application.

Best,

Sunny

[#5]

Ciao Sunny,

many many thanks. You calculated correctly but I think that I made a mistake in my explanation.

I re-attached a new modificated file with:

1 sheet of the sample of the original database template

2 the development & calculation

3 the expected output by sku

I hope that it will be more clear.

Could you please write me directly in the email the formula for the output price effect abs and %?

I have some trouble to open the qvw files that you attach in the post.

Sorry and Thanks for your patiance and support!!!

ciao!!

mb

Il giorno 05/giu/2015, alle ore 21:12, sunindia <qcwebmaster@qlikview.com> ha scritto:

How to mix aggr() and if statements correctly - price effect calculation

reply from sunindia <https://community.qlik.com/people/sunindia?et=watches.email.thread> in New to QlikView - View the full discussion <https://community.qlik.com/message/791149?et=watches.email.thread#791149>

Is this what you want?

https://community.qlik.com/servlet/JiveServlet/downloadImage/2-791149-88526/Capture.PNG <https://community.qlik.com/servlet/JiveServlet/showImage/2-791149-88526/Capture.PNG>

I was not able to get the headers right for the last three columns, but you can place a text box on top of them.

Check it out in the attached application.

Best,

Sunny

Reply to this message by replying to this email, or go to the message on Qlik Community <https://community.qlik.com/message/791149?et=watches.email.thread#791149>

Start a new discussion in New to QlikView by email <mailto:discussions-community-qlikview-newtoqlikview@qliktech-public-v7.hosted.jivesoftware.com> or at Qlik Community <https://community.qlik.com/choose-container.jspa?contentType=1&containerType=14&container=2003&et=watches.email.thread>

Following How to mix aggr() and if statements correctly - price effect calculation <https://community.qlik.com/message/791149?et=watches.email.thread#791149> in these streams: Inbox

© 1993-2014 QlikTech International AB | Copyright & Trademarks <http://www.qlikview.com/us/info/copyright> | Privacy <http://www.qlikview.com/us/info/privacy> | Terms of Use <http://www.qlikview.com/us/info/terms-of-use> | Software EULA <http://www.qlikview.com/us/info/software-eula>

[#6]

ciao Sunny,

did you receive my second attachment with my previous post??

.. I can't send the file using the layout of this forum?

I don't know why...

Hope you found a solution for me .. I'm going mad with this problem...

Thanks in advance..

ciao!!

[sunny_talwar]

Did you attach the new file with your original post with the same name???? I wasn't sure where to find it

[#8]

Ciao Sunny

I tried also with advanced editor but I can't attach the file..

The file is named

price effect correction & output

I sent it in a mail..did you receive it?

Ciao

Mb

[sunny_talwar]

Nope, I have not received any email my friend.

[#10]

Ciao Sunny,

I can't attach anything..could you please give me an indication on how to send you the new file?

Thanks in advance

ciao