topic Re: How to display end of period inventory in line chart ? (no sum) in QlikView

How to display end of period inventory in line chart ? (no sum)

Cyriltra — Sat, 16 Nov 2024 19:02:36 GMT

Hello,

each row in my database record is like this:

Processing Date (dd/mm/yy)	Region	Sales	EOP Inventory
21/02/20	Americas	1000	300
22/02/20	Europe	1500	400
23/02/20	Asia	500	250

And i have a master calendar that derives weeks, month, quarter, years etc. from [Processing Date] field from past 3 years to year + 1 (so 2021 here)

I then display my sales in line chart (weekly, monthly, yearly) and using below definition which is working fine:

Sum({$<[454.year]={$(=Only([454.year]))}>}[Sales]) for current year

Sum({$<[454.year]={$(=Only([454.year])-1)}>}[Sales]) for year -1

Now want to also do a line chart but for my inventory. But I cannot use sum as it does not make sense here. So depending on the period set as dimension (ie can be by week, or quarter or year etc.), I want to display the value at the end of this period.

Let's say my dimension is to display by week (so week 1 to week 52), I want the chart to display the inventory at the end of each week).

Browsing around, it seems a possible solution is to use firstSortedValue(), but I 'm not sure how to use it. I tried below:

firstSortedValue([End Inv_C US$], -[Processing Date])

But it does not work....

Also, same as i did for my sales, I also need to display my end of period inventory for the year-1

Thank you

Re: How to display end of period inventory in line chart ? (no sum)

DavidM — Wed, 26 Feb 2020 11:14:25 GMT

Do you have days in you calendar? Use the last day of week (Fri, Sun, whatever you consider as last) in the set analysis, something like this:

Sum({$<[Day]={'Sun'}>}[EOP Inventory]) - if you have weeks as dimension in your chart, it will show you value only for Sunday.

Re: How to display end of period inventory in line chart ? (no sum)

Cyriltra — Fri, 28 Feb 2020 01:40:17 GMT

Hi,

thank you it's working fine when i display my chart with weekly dimensions, but how can i do with Month and Quarter ?

Re: How to display end of period inventory in line chart ? (no sum)

DavidM — Fri, 28 Feb 2020 11:06:57 GMT

I suppose you want always the last day, right? Try this:

Sum({$<[Date]={"$(=Max(Date))"}>}[EOP Inventory])

Re: How to display end of period inventory in line chart ? (no sum)

sunny_talwar — Fri, 28 Feb 2020 15:49:04 GMT

This looks to be okay... what dimensions do you have in your chart where you are using this?

FirstSortedValue([End Inv_C US$], -[Processing Date])

Re: How to display end of period inventory in line chart ? (no sum)

Brett_Bleess — Tue, 31 Mar 2020 17:23:33 GMT

You received a couple more posts and no further responses, so I am assuming you got a solution, please be sure you close out your thread by using the Accept as Solution button on the post(s) that helped you with things, or if you figured it out on your own, post what you did and then mark that post. Marking things closes out the thread and gives the posters credit for the help and lets other Members know what worked. If you still have questions, please leave an update post.

Regards,
Brett