topic Re: Pick only value for latest date in QlikView

Pick only value for latest date

akpofureenughwu — Sun, 07 May 2017 14:06:01 GMT

Good day one

I have a set of data I need to pick only the latest data

Location	01/01/2017	02/01/2017	03/01/2017
Area 1	150	100	120
Area 2	100	40	60
Area 3	70	100	150

I used the crosstable function to resolve my data

Something like this

At the backend

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

[42736],

[42737],

[42738]

from	DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

	Date(Num#(Date)) as Date,
	Month(Num#(Date)) as Month,
	'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
	MonthName(Num#(Date)) as MonthYear,
	Year(Num#(Date)) as Year,
	Day(Num#(Date)) as Day,
	Value

Resident Fox;

Drop Table Fox;

All I need is how I can only display value based on latest date

Re: Pick only value for latest date

Anonymous — Sun, 07 May 2017 14:22:30 GMT

TEMP: LOAD max(Date) as MAXDATE resident FinalTable;

let varmaxdate = peek('MAXDATE');

drop Table TEMP;

Noconcatenate

FINALFINALTABLE:

LOAD * resident FinalTable

where Date=$(varmaxdate);

drop table FinalTable;

let varmaxdate=null();

Re: Pick only value for latest date

akpofureenughwu — Sun, 07 May 2017 14:35:43 GMT

roharoha‌

The script you suggest above worked... Can I display the above result (the value based on the latest date) and the highest value in the given time frame?....

Thank you sir

Re: Pick only value for latest date

Anonymous — Sun, 07 May 2017 14:48:29 GMT

add at the end:

FINALFINALFINALTABLE:

Load

firstsortedvalue([Location],-Value) as [Location] ,

firstsortedvalue(Date(Num#(Date)),-Value) as Date,

firstsortedvalue(Month(Num#(Date)),-Value) as Month,

firstsortedvalue('Q' & Ceil(Month(Num#(Date))/3),-Value) as Quarter,

firstsortedvalue(MonthName(Num#(Date)),-Value) as MonthYear,

firstsortedvalue(Year(Num#(Date)),-Value) as Year,

firstsortedvalue(Day(Num#(Date)),-Value) as Day,

max(Value) as Value

resident FINALFINALTABLE;

drop Table FINALFINALTABLE;

Re: Pick only value for latest date

akpofureenughwu — Sun, 07 May 2017 16:01:49 GMT

I included the latest script you suggested ....

FINALFINALFINALTABLE:

Load

firstsortedvalue([Location],-Value) as [Location] ,

firstsortedvalue(Date(Num#(Date)),-Value) as Date,

firstsortedvalue(Month(Num#(Date)),-Value) as Month,

firstsortedvalue('Q' & Ceil(Month(Num#(Date))/3),-Value) as Quarter,

firstsortedvalue(MonthName(Num#(Date)),-Value) as MonthYear,

firstsortedvalue(Year(Num#(Date)),-Value) as Year,

firstsortedvalue(Day(Num#(Date)),-Value) as Day,

max(Value) as Value

resident FINALFINALTABLE;

drop Table FINALFINALTABLE;

I practically had not data to visualized....

Re: Pick only value for latest date

Anonymous — Sun, 07 May 2017 16:16:14 GMT

then you should post some sample data...

Do you need only 1 max date and 1 max value?

Re: Pick only value for latest date

akpofureenughwu — Sun, 07 May 2017 17:08:57 GMT

Here is the sample data...

in the app I am building , I'm to visualize the sales of each location at the latest date ( use of bar chart ) as well as

the maximum sales of the available period say in day 7 Area 1 sold 6,000 and area 2 sold 7,000.. the sum is 13,000 .. If this is the peak then I want to visualize this figure

.. ( in the sample file from January to April 2017)

Regards

Re: Pick only value for latest date

Anonymous — Sun, 07 May 2017 17:29:43 GMT

frontend or backend solution?

Re: Pick only value for latest date

akpofureenughwu — Sun, 07 May 2017 17:32:56 GMT

I would prefer a frontend solution since this will allow me to load all the available data

Re: Pick only value for latest date

akpofureenughwu — Mon, 08 May 2017 12:47:02 GMT

roharoha‌

Good day sir,

Still waiiting for your response sir,

Regards

Re: Pick only value for latest date

Anonymous — Mon, 08 May 2017 17:33:25 GMT

btw, please don't rush me... as long as I'm at work I won't answer...

Re: Pick only value for latest date

akpofureenughwu — Tue, 09 May 2017 09:59:29 GMT

roharoha‌

Thank yo sir... sorry for the panic message man.. Just one more thing (please take your time.. I'm not rushing you bro)..

How can I display the highest value of (Max(Aggr(sum(Valuefield_),Datefield_))) for the given period,

My client would love to see the peak sales value for the given period.. (more like Peak Sales YTD)

Thank you for your time and effort. I truly appreciate

Regards

Re: Pick only value for latest date

Anonymous — Tue, 09 May 2017 16:55:35 GMT

Hi, I suppose that your period starts at January 1st...

Max(Aggr(sum({<Datefield_={"=InYearToDate(Datefield_,Today(),0)"} >} Valuefield_),Datefield_))

Re: Pick only value for latest date

Anonymous — Tue, 09 May 2017 16:58:58 GMT

or like that:

Max(Aggr(sum({<Datefield_={">=$(=Yearstart(Today()))"} >} Valuefield_),Datefield_))

Re: Pick only value for latest date

Anonymous — Thu, 11 May 2017 16:35:15 GMT

is your question answered or not?

Re: Pick only value for latest date

akpofureenughwu — Fri, 12 May 2017 12:16:55 GMT

roharoha‌

My answer has been answered, I just got a new requirement from my client,

From the script you suggest to me :

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

[42736],

[42737],

[42738]

from	DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

	Date(Num#(Date)) as Date,
	Month(Num#(Date)) as Month,
	'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
	MonthName(Num#(Date)) as MonthYear,
	Year(Num#(Date)) as Year,
	Day(Num#(Date)) as Day,
	Value

Resident Fox;

Drop Table Fox;

TEMP: LOAD max(Date) as MAXDATE resident FinalTable;

let varmaxdate = peek('MAXDATE');

drop Table TEMP;

Noconcatenate

FINALFINALTABLE:

LOAD * resident FinalTable

where Date=$(varmaxdate);

drop table FinalTable;

let varmaxdate=null();

Is it possible to replicate this script in the front end? so that at the back end, all I have is

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

[42736],

[42737],

[42738]

from	DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

	Date(Num#(Date)) as Date,
	Month(Num#(Date)) as Month,
	'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
	MonthName(Num#(Date)) as MonthYear,
	Year(Num#(Date)) as Year,
	Day(Num#(Date)) as Day,
	Value

Resident Fox;

Drop Table Fox;

Thank you for your unprecedented support

Regards

Re: Pick only value for latest date

akpofureenughwu — Fri, 12 May 2017 12:36:09 GMT

For clarity,

With this script...

Fox:

CrossTable (Date, Value, 1)

LOAD [Location],

[42736],

[42737],

[42738]

from	DAILY Sales.xlsx]

(ooxml, embedded labels, table is Sample);

FinalTable:

Load [Location],

	Date(Num#(Date)) as Date,
	Month(Num#(Date)) as Month,
	'Q' & Ceil(Month(Num#(Date))/3) as Quarter,
	MonthName(Num#(Date)) as MonthYear,
	Year(Num#(Date)) as Year,
	Day(Num#(Date)) as Day,
	Value

Resident Fox;

Drop Table Fox;

I can sort out the latest date with this expression

' maxstring(Date) ' (from the front end)

Is it possible to get the latest value for the front end?...

Re: Pick only value for latest date

Anonymous — Fri, 12 May 2017 12:49:04 GMT

if you like to get the maximum date overall, use

max(total date)

in frontend, that should be all

Re: Pick only value for latest date

akpofureenughwu — Fri, 12 May 2017 16:34:43 GMT

Thank you sir, but that doesn;t answer my question...

Take this for example...

Date	Location	Sales	Refund
01/01/2017	Area 1	100	20
01/01/2017	Area 2	150	30
02/01/2017	Area 1	50	10
02/01/2017	Area 2	160	0

My client need to see just the latest date value ... (from the table above ) is Area 1 .... 50, Area 2 160 ( for sales)

I need this done from the front end sir

Thank you roharoha stalwar1

Re: Pick only value for latest date

sunny_talwar — Fri, 12 May 2017 16:44:35 GMT

May be this

FirstSortedValue(Sales, -Date)

FirstSortedValue(Aggr(Sum(Sales), Date, Location), -Aggr(Date, Date, Location))