topic Re: Show last months data. in QlikView

Show last months data.

amithmurali — Sun, 30 Aug 2015 08:40:14 GMT

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.

Re: Show last months data.

swuehl — Sun, 30 Aug 2015 10:26:58 GMT

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.

Re: Show last months data.

sunny_talwar — Sun, 30 Aug 2015 11:16:36 GMT

May be you can use this as your expression:

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

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

Look at the attached sample.

Best,

Sunny

Re: Show last months data.

amithmurali — Sun, 30 Aug 2015 11:23:55 GMT

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.

Re: Show last months data.

qlikviewwizard — Sun, 30 Aug 2015 11:26:44 GMT

Hi Amith,

Please attach the sample file.

Re: Show last months data.

amithmurali — Sun, 30 Aug 2015 11:49:30 GMT

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.

Re: Show last months data.

swuehl — Sun, 30 Aug 2015 12:42:51 GMT

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

Re: Show last months data.

maksim_senin — Mon, 31 Aug 2015 01:44:13 GMT

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