Show last months data.

[amithmurali]

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. 

[swuehl]

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.

[sunny_talwar]

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

[amithmurali]

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.

[qlikviewwizard]

Hi Amith,

Please attach the sample file.

[amithmurali]

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.

[swuehl]

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

[maksim_senin]

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