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

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

DesertMatt11 — Mon, 03 Jan 2022 09:20:41 GMT

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

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

tresB — Mon, 03 Jan 2022 09:41:06 GMT

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

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

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

DesertMatt11 — Mon, 03 Jan 2022 10:01:27 GMT

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?

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

tresB — Mon, 03 Jan 2022 10:25:56 GMT

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)))

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

DesertMatt11 — Mon, 03 Jan 2022 10:46:06 GMT

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

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

DesertMatt11 — Mon, 03 Jan 2022 10:55:04 GMT

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

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

tresB — Mon, 03 Jan 2022 11:21:38 GMT

May be:

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

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

DesertMatt11 — Mon, 03 Jan 2022 11:56:25 GMT

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