Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Partner - Creator II

Show last months data.

hi guys,

In my dashboard, i have to show the inventory values. It is calculated on a monthly basis. we cannot sum up all the months data as it will duplicate. so for example if i want to show the august months data, i have to select august month.

Here is the situation. Let us assume that today is sept 1, and the data in the db will be till august only . as i have used the formula " sum({<Year = {\$(=max(Year))}, NumMonth = {"\$(vMaxNumMonth)"}>} StockValue) ".

(where vMaxNumMonth =max({<Year = {"\$(vMaxYear)"}>}NumMonth))

The stock values will be updated only in the end of the month. so when the date is 1st september, the stock charts will show no data. but i need to show them the august data. But when they select the august month, it should show august stock value not the july stockvalue.

7 Replies
MVP

Maybe something like

vMaxNumMonth =max({<Year = {"\$(vMaxYear)"}, StockValue = {"*"} >}NumMonth)

I don't know your data model, so this may work or maybe not. Basic idea is to find max(NumMonth) where there are values for StockValue.

MVP

May be you can use this as your expression:

=FirstSortedValue({<StockValue = {"<>0"}, Year = {\$(=max(Year))}>}StockValue, -NumMonth)

or

=FirstSortedValue({<StockValue = {"<>0"}>}StockValue, -DateField)

Look at the attached sample.

Best,

Sunny

Partner - Creator II
Author

The stock value will be updated only in the last day of the month. so when it is September it should show aug stock value. When the maximum month selected doesnt contain any value then it should show the previous months value.

Master II

Hi Amith,

Partner - Creator II
Author

Here You can notice that the Sales value will be present in the september but the stock value will not be there. thats because the stock value is updated only in the month end while the sales value will be updated daily.

MVP

Ok, that's a different setting than you initially described with your set analysis expression, here you want to group Stock and Sales values per month.

This should show you the stock value even in the latest month (assuming a chart with dimension Month and ordered Month desc):

=if(count(StockValue),sum(StockValue), above(sum(StockValue) ))

Partner - Creator III

Hi,

It seems that you need to have a like-for-like comparison. This can be done via set analysis using a date dimension as a filter, e.g. {<Date={"\$(LFLPeriod)"}>} where LFLPeriod=">=Date0<=Date1".

I hope it's clear that you just need to put real dates instead of Date0 and Date1.

Best regards,

Maxim

Community Browser