Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Cyriltra
Contributor
Contributor

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

Hello,

each row in my database record is like this:

Processing Date (dd/mm/yy)RegionSalesEOP Inventory
21/02/20Americas1000300
22/02/20Europe1500400
23/02/20Asia500250

 

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

Labels (3)
5 Replies
DavidM
Partner
Partner

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.

Cyriltra
Contributor
Contributor
Author

Hi, 

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

DavidM
Partner
Partner

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

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

 

 

 

sunny_talwar

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])
Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.