topic Re: Latest Daily/Month Production against Previous Day/Month in QlikView

Latest Daily/Month Production against Previous Day/Month

akpofureenughwu — Mon, 20 Mar 2017 15:22:20 GMT

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)

Year	Month	Station	Figure (Kg)
2010	January	Edo	56,000
		Delta	67,000
	February	Edo	78,000
		Delta	69,000

Sample Data (cuurent day vs previous day)

Date	Edo	Delta	Lagos
2013/09/29	30	50	100
2013/09/30	60	25	150

Thanks

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

sunny_talwar — Mon, 20 Mar 2017 16:02:36 GMT

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

akpofureenughwu — Mon, 20 Mar 2017 16:12:02 GMT

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

sunny_talwar — Mon, 20 Mar 2017 16:17:07 GMT

I like this

The As-Of Table‌

Have you already considered this?

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

akpofureenughwu — Mon, 20 Mar 2017 16:27:37 GMT

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

sunny_talwar — Mon, 20 Mar 2017 16:35:44 GMT

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

akpofureenughwu — Mon, 20 Mar 2017 16:38:25 GMT

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

sunny_talwar — Mon, 20 Mar 2017 16:42:37 GMT

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

akpofureenughwu — Mon, 20 Mar 2017 16:49:47 GMT

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

sunny_talwar — Mon, 20 Mar 2017 16:58:37 GMT

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

akpofureenughwu — Tue, 21 Mar 2017 08:33:11 GMT

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

sunny_talwar — Wed, 22 Mar 2017 09:23:34 GMT

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

akpofureenughwu — Thu, 23 Mar 2017 08:26:36 GMT

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