Sales for Products which have been sold in the same year of their Production (Year in Dimension and in expression) in pivot table

[DesertMatt11]

Happy New Year to all of you!

To make my start of the year happy, I really would require your urgent help please, as I'm stuck with this one (getting either wrong data or none).

Am trying to show the production year of the products as dimension in pivot table and then one expression which counts the products made in this year (done) and one more measure showing the number of products sold AND manufactured in the same year as the related dimension - here I'm stuck - tried everything and cannot find any message here in the forum which worked... I need below output from attached excel and qvw (please adjust your paths in the loadscript to your desktop) 

Created also a linktable in the testdata as this is what we have in our quite complex real data - hope all else is self explanatory?

Could any of you wizards please point me in the right direction?

Thank you so much

Matt

[tresesco]

A front-end easy solution could be using an expression, like:

Count(DISTINCT (If( Year(MakeDate)=Year(SalesDate), Product)))

[DesertMatt11]

Thank you  very much Tresesco for your fast reply.

I'm afraid I need a sum expression as the same product could be also sold several times in the same year as it was manufactured and I tried the if condition like yours followed by a sum expression - but this also didn't work - am I missing something? 

[tresesco · Accepted solution]

Sorry, didn't get you. If you need to count how many times the sales has happened (i.e. - counting the products multiple times when so), you should probably remove the 'distinct' and try like:

Count( (If( Year(MakeDate)=Year(SalesDate), Product)))

[DesertMatt11]

Thank you Tresesco

This works - so simple I'm really ashamed 😞 (but at the same time grateful for your fast and very helpful reply!!!)

Still cannot understand why I could not get the Sum of Sales Pcs with this logic though?

THANK YOU VERY MUCH AGAIN Tresesco - I really appreciate it (even though I don't fully understand it...)

regards

Matt

[DesertMatt11]

Any idea how I would get the Sales Value for all Products which have been produced and Sold in the same year (if we would have the SalesPrice also in the Sales Dat - not only Pcs)?

Thank you 

Matt 

[tresesco]

May be:

Sum( (If( Year(MakeDate)=Year(SalesDate), Sales)))

[DesertMatt11]

Yes exactly - figured it also out in the meanwhile.... even added now a setanalysis after sum and before the If... and it seems to give the expected results - am still checking ...

You put me on the right track - thank you so much Tresesco - never saw this construct before (with if after Sum etc.) - so much to learn always...

kind regards

Matt