topic Re: data extraction problem in QlikView

data extraction problem

Fri, 12 Feb 2016 08:08:08 GMT

Date Field	AUM
1	10
4	20
5	21
6	22
7	23
9	24
10	25
11	26
15	27

i have data like this what i want max date in one field of pivot and previous in other.

Catch here is that when i select 15 as maximum in previous column i want "11" date also its value specifically.

i did make variable of max through which i can extract 15 but to extract 11 is bit problem for me also not able to extract its SUM of that day AUM.

Re: data extraction problem

kaushiknsolanki — Fri, 12 Feb 2016 08:19:27 GMT

Hi,

You can use the Max Function to get the result you need.

Use below function.

Max(Date, -1)

Regards,

Kaushik Solanki

Re: data extraction problem

Fri, 12 Feb 2016 08:21:29 GMT

i did use the same but i select Max(date,-1) it gives me 14 date instead of 11 i need 11 and its data

Re: data extraction problem

kaushiknsolanki — Fri, 12 Feb 2016 09:09:35 GMT

Hi Daniyal,

You must have missed the comma.

Regards,

Kaushik Solanki

Re: data extraction problem

Fri, 12 Feb 2016 10:03:09 GMT

sir,

these are actual dates but i converted them into numbers,i could not find a solution if you could please share Qvd which is workable and also share other formula if it is not working.

Re: data extraction problem

kaushiknsolanki — Fri, 12 Feb 2016 10:17:34 GMT

Here is the application which shows the expression.

Regards,

Kaushik Solanki

Re: data extraction problem

sunny_talwar — Fri, 12 Feb 2016 10:43:54 GMT

Do you happen to have master calendar in your application where you have created all the dates, but you are looking for date which have last AUM? You can give this a try:

Max({<AUM = {"=Len(Trim(AUM)) > 0"}>} Date, -1)

Re: data extraction problem

Fri, 12 Feb 2016 11:05:38 GMT

i have date field not a master calendar and i put these dates in List box and convert them in days number number by using =day(Dates) but my dates are not constant from 1-31 my dates have missing dates of Saturday and Sunday as i given above.

i do not want missing dates of Saturday and Sundays.

Re: data extraction problem

sunny_talwar — Fri, 12 Feb 2016 12:10:20 GMT

Have you given try to the above expression? Is it still not working?

Re: data extraction problem

kaushiknsolanki — Fri, 12 Feb 2016 12:13:00 GMT

Dear Have you checked the one which I have given?

Is that what you want or not?

Regards,

Kaushik Solanki

Re: data extraction problem

Fri, 12 Feb 2016 13:18:28 GMT

working fine Thanks

Re: data extraction problem

Mon, 15 Feb 2016 05:43:18 GMT

problem is solved for dates but not able to sum AUM

Kaushik.solanki when i use your qvd for dates it works fine but when i use this formula =SUM({$<[Execution Date] = {"<$(=max([Execution Date]))"}>}AUM/1000000) for last day AUM it sum all dates AUM could you please help me out in this.

i am new to qlikview therefore having some problems.

Re: data extraction problem

Mon, 15 Feb 2016 08:06:25 GMT

i use this expression but it gives me 0 please check did i miss any comma or anything else to calculate last day AUM

=sum({<[Execution Date]={'$(Vmaxdate)'},max({<[Execution Date] = {"<$(=max([Execution Date]))"}>}[Execution Date])>}AUM)

Re: data extraction problem

sunny_talwar — Mon, 15 Feb 2016 13:44:54 GMT

You seem to have an extra max in there and also you have Execution Date twice. Do you need it to be equal to Vmaxdate or < Max([Execution Date])?

=Sum({<[Execution Date] = {'$(Vmaxdate)'}, [Execution Date] = {"<$(=max([Execution Date]))"}>}[Execution Date])>} AUM)

Re: data extraction problem

Mon, 15 Feb 2016 14:14:54 GMT

Sir let me explain,

i have data above with dates not in serial for example i have maximum date 15 with AUM 27 i did extract this using formula =Sum({$<[Execution Date]={'$(Vmaxdate)'}>}AUM/1000000) vmaxdate is my variable the problem arise when

i want to extract date 11 and its AUM.

i did extract date using this expression =date(max({<[Execution Date] = {"<$(=max([Execution Date]))"}>}[Execution Date]),'DD-MMM-YYYY')

but if i want to extract AUM of 11 which is 26 not able to do it,i do not know where i am doing mistake i just want to extract AUM of that day using any formula.

Re: data extraction problem

Mon, 15 Feb 2016 14:36:30 GMT

sir please share AUM numbers with me also as you did for date also share QVD if possible

Re: data extraction problem

kaushiknsolanki — Tue, 16 Feb 2016 14:55:26 GMT

Hi Daniyal,

I havent use any QVD. I have used the Inline table and created data in QlikView itself.

Regards,

Kaushik Solanki

Re: data extraction problem

Wed, 17 Feb 2016 04:43:38 GMT

Hi,

i am talking about QVD which you attached for extracting Dates "Test.QVD" it only extracts dates i want AUM also should be extract same way as you did for dates.

i would be really great if you attached test QVD as well for the AUM as you did for dates.