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: 
desertmatt
Contributor III
Contributor III

Show our Customers' Sales within each Year after Our Sales Date to them

Dear Community,

I want to analyze how long it takes till our customers sell our products invoiced to them in various years.

The final Table should look something like this 

Sales-Sold.jpg

It seemed so simple but it's not happening

I created in the load script the Year fields from the Invoice dates

SalesDateYear as Year(Date(InvoiceDate)) (the Date when WE sold to our customers)

SoldDateYear as Year(Date(CustomerSoldDate) (the Dates when our customer sold to their customers)

No Selection.

As Dimension I'm using the SalesDateYear (2014, 2015, 2016, etc.)

In the expression for the different Years within and  after our Invoice Year, I tried:

=Sum({< SoldDateYear = {"=AddYears (SalesDateYear,1)"}>} SalesPrice)/Sum(SalesPrice)

but this is not working all other trials also failed (like aggr over the SalesDateYear etc.)

I'm sure for our experts here this is very easy but it escapes me...

Your help would be VERY much appreciated

Thank you very much

Matt

Labels (3)
2 Solutions

Accepted Solutions
sunny_talwar

Does this look like what you want?

image.png

View solution in original post

sunny_talwar

This

=Sum({<InvType-= {"Memo"}>} If(SoldateYear=SalesDateYear+2, SoldPrice))

As a general rule, Set analysis can only be used within an aggregation function... so use it right after Sum(... rather than within your If statement 

View solution in original post

13 Replies
sunny_talwar

May be provide some sample data behind the output and share the script you have tried to see what you have done and propose changes to it?

desertmatt
Contributor III
Contributor III
Author

Hello Sunny

thanks for your fast reply. As the data comes from an Oracle Database and there are many more fields it is not possible to supply the actual data. I tried to explain as detailed as possible what I'm trying to do.

About the data structure:

2 tables (actually many more - but just trying to figure this out in separate qvw)

NetSale Table with Customer, SKU No., Invoice No, Invoice Date, Sales Price

NetSold Table with Customer, SKU No., Invoice No, Invoice Date, CustomerSoldDate, Sales Price

CustomerStock with Customer, SKU No., Invoice No, Invoice Date, Sales Price (Showing all items in stock with each customer as per today)

There is a SynKey with Customer, SKU NO. , Invoice No.

I get all data for all other analysis as wanted - only how to make sure that the "1. Year (2. Year, 3. Year, etc.) " Column only shows items which our customers have sold within one (2, 3, etc.) year of the Year shown in the Dimension

Hope this makes it clearer?

desertmatt
Contributor III
Contributor III
Author

Hello Sunny

I made a simple excel with some sample data and loaded it into attached qvw

I tried again with AddYears but get same data in all columns for 1., 2., 3., etc Years for Sold data 😞

The empty CustomerSoldDate in the excel means the items haven't been sold by our customers till date.

What am I missing?

PLEASE!

 

desertmatt
Contributor III
Contributor III
Author

This is the excel for above qvw (couldn't figure out how to attach 2 files in a post)

desertmatt
Contributor III
Contributor III
Author

Dear Sunny and other Experts,

I got so many views but not a single answer or suggestion?

Either this is really a not so easy problem to solve - or did I do something wrong?

I manually calculated the proper outcome in excel for the small sample excel source and qvw attached above.

Sales-Sold-manual-result.jpg

Is there any way to achieve the same in Qlikview? (Last remaining Customer stock column I get myself properly but the values for the items sold in the 1.(same as invoice year), 2. (invoice Year +1) ,3. (invoice year +2), etc years after the invoice date year are not showing properly. I cannot think of any other way then the addyears method...

Your help would be very much appreciated

Thank you very much

Matt

 

sunny_talwar

Does this look like what you want?

image.png

desertmatt
Contributor III
Contributor III
Author

SUNNY - YOU ARE THE MAN!!!! 🙂

this is EXACTLY what I was searching for - but I'm not getting this since days - also tried with IF(SalesDateYear=SoldDateYear, Sum(SoldPrice)) - but not with Sum(If()) - a small but very relevant difference...;-)

Thank you so much - that's really a good Christmas present 🙂

Thank you very much for looking into this!

Kind regards

Matthias

 

sunny_talwar

Awesome, I am glad I was able to help.

Best,
Sunny

desertmatt
Contributor III
Contributor III
Author

Sorry Sunny

one more (hopefully) small problem occurred now when I wanted to implement this with our real data:

I used before a Set expression to exclude some invoice types and I don't know where to place this now in your Sum(If()) solution?

I tried =Sum(If(SoldateYear=SalesDateYear+2,{<InvType-= {"Memo"}>}SoldPrice)) but this doesn't work

After all the other trial and error my brain goes blank now 😞

Is there a proper way to integrate these 2 parts?

Thank you already in advance and sorry to keep bothering...

Matt