12 Replies Latest reply: Mar 23, 2017 4:26 AM by Akpofure Enughwure

# Latest Daily/Month Production against Previous Day/Month

Good day everyone,

I have a set of data that captures production in daily and month interval.

I would love to visualize the data showing latest( current) day production vs previous date productions as well as current month vs previous month.

Sample data ( current month vs previous month)

YearMonthStationFigure (Kg)
2010JanuaryEdo56,000
Delta67,000
FebruaryEdo78,000
Delta69,000

Sample Data (cuurent day vs previous day)

DateEdoDeltaLagos
2013/09/293050100
2013/09/306025150

Thanks

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Are you running into an issue? I wasn't sure what the problem is by reading your post?

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Hello sunny... Thanks for your response

My challenge is how to approach it.. (write the script)

I could use the point in time reporting/ set analysis approach but I don't know if you have a better approach

thanks

• ###### Re: Latest Daily/Month Production against Previous Day/Month

I like this

The As-Of Table

Have you already considered this?

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Thanks I just looked at the blog you linked.. The 'as of month' approach is not quite giving me my desired approach.

Look at it this way... ( Saw this in a book 'Qlikview 11 for developers' written by M. Garcia, Barry Harmsen  )

To embed actual calculations into the field value definition in a set expression, we use the Dollar Sign Expansion (DSE) syntax. The final expression would be as follows:

Sum({\$<Year = {\$(=Max(Year))}>} [# Departures Performed])

If we want to get the record set corresponding to the previous year to whatever year the user selects, we would use:

Sum({\$<Year = {\$(=Max(Year)-1)}>} [# Departures Performed])

In the expression above, they were able to compare present year  figures  with previous year figures

I need to do such for present day vs previous day as well as present month vs previous month

Thanks

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Is date or month one of the dimensions in the chart you are wanting to compare the two?

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Yes..

My desired design is to use two gauges ( on showing present value) and the other (showing previous month/day value)

Regards

• ###### Re: Latest Daily/Month Production against Previous Day/Month

So there is no dimension then? You can use set analysis in that case....

Previous Day

Sum({<Date = {"\$(=Date(Max(Date) - 1))"}>} Measure)

Previous Month

Sum({<Date = {"\$(='>=' & Date(MonthStart(Max(Date), -1)) & '<' & Date(MonthStart(Max(Date))))"}>} Measure)

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Alright sir

And I guess the current day is

Sum({<Date = {"\$(=Date(Max(Date) ))"}>} Measure)

and

current month

Sum({<Date = {"\$(='>=' & Date(MonthStart(Max(Date), )) & '<' & Date(MonthStart(Max(Date))))"}>} Measure)

right?

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Current month would be something like this

Sum({<Date = {"\$(='>=' & Date(MonthStart(Max(Date), 0)) & '<' & Date(MonthStart(Max(Date), 1)))"}>} Measure)

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Good morning sir,

I  tried the script you suggested yesterday it didn't give me the desired result (see attached)

Here is the script

LIB CONNECT TO 'Planning';

LOAD *,

if( Isnum(Date), 'Q')& Ceil(Month(Date)/3) as Quarter;

LOAD *,

Date(Date#(Year&Month, 'YYYYMMMM')) as Date;

LOAD if(Len(Trim("Year")) = 0, Peek ("Year"), "Year") as "Year",

if(Len(Trim("Month")) = 0, Peek ("Month"), "Month") as "Month",

"Field",

Figures;

SQL SELECT "Year",

"Month",

"Field",

Figures

FROM " Planning".dbo.Planning;

I would love to compare the current month vs previous month for each field ( there are like three fields)

Regards

• ###### Re: Latest Daily/Month Production against Previous Day/Month

Which expression are you trying? Current Month or Previous Month? Also, are you making selections in Month or Year field?

• ###### Re: Latest Daily/Month Production against Previous Day/Month

I'm trying the current month vs previous month....

I am making selection in date field since the date field is the combination of both month and year field..

Regards