Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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?
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?
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!
This is the excel for above qvw (couldn't figure out how to attach 2 files in a post)
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.
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
Does this look like what you want?
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
Awesome, I am glad I was able to help.
Best,
Sunny
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