topic Need max year, max month and max day using set analysis in QlikView

Need max year, max month and max day using set analysis

Wed, 29 Dec 2010 08:31:00 GMT

Dear All,

i need to show max year, max month & max day of sales, so i used following function , but its not showing the amount of sales.

Pls see below function.

=Sum({$<Year = {$(=max(Year))}, Month = {$(=month(addmonths(max(Month) , -1)))},Day = {$(=max(Day))}>} Amount)

Regards,

Antony.

Need max year, max month and max day using set analysis

marcohadiyanto — Wed, 29 Dec 2010 11:08:51 GMT

hi, if you want max month, why your month (-1) ?

Need max year, max month and max day using set analysis

syed_muzammil — Wed, 29 Dec 2010 11:13:58 GMT

Hi,

Can't you use just a date field instead of all this. I mean

Date={$(=Date(Max(Date)))}.

Need max year, max month and max day using set analysis

Wed, 29 Dec 2010 12:06:31 GMT

Hi,

-1 is the default for max month

if you max month is Dec then -1 will show the month of "Dec" , when you mention -2 then it will show the previous month " Nov".

Please use the condition & check in your application.

Need max year, max month and max day using set analysis

Wed, 29 Dec 2010 12:10:05 GMT

i used the below conditon, but its showing "0"

sum({$<Date={$(=Date(max(Date)))}>}Amount)

Need max year, max month and max day using set analysis

deepakk — Wed, 29 Dec 2010 13:00:24 GMT

Hi,

Create a MonthNum at script level

eg : Num(Month) as MonthNum

And use the below expression

Sum({$<Year = {$(=max(Year))}, MonthNum = {$(=month(max(MonthNum)-1))},Day = {$(=max(Day))}>} Amount)

I hope this helps....

Need max year, max month and max day using set analysis

Wed, 29 Dec 2010 13:10:54 GMT

Hi Deepak,

Only for Month or i have to do it for year & day also.

Need max year, max month and max day using set analysis

Wed, 29 Dec 2010 13:23:35 GMT

Hi deepak,

I used the below expression as per your instruction but its showing "0".

Sum({$<Year = {$(=max(Year))}, MonthNum = {$(=month(max(MonthNum)-1))},Day = {$(=max(Day))}>} Amount)

upto Month its showing correctly when i add "Day ={$(=max(Day))}" in the above expression then its showing the Amount "0"

Need max year, max month and max day using set analysis

deepakk — Thu, 30 Dec 2010 04:48:51 GMT

HI,

First Check out what Date your getting in Max(Year) & Max(MOnthNum) & Max(Day)...

if its 2010 10 1 then check if you have any Amount for that particular.

Need max year, max month and max day using set analysis

deepakk — Thu, 30 Dec 2010 04:51:29 GMT

hi,

Also check the formatting of dates.

eg :

Day in backend would be 01 or 02 but when you say Max(Day) it might become 1 or 2.

Thia might also be the reason for 0 Amount.

Need max year, max month and max day using set analysis

Thu, 30 Dec 2010 06:39:32 GMT

Hi Deepak,

I checked both the condition which you mentioned on above , its fine.

When i use the below condition then its showing the correct Amount for max year, month & date.

=sum(if(Date=DateMax,Amount))

Also one more query.

The following expressions are same or different, Because i'm getting different amount for below expressions.

1) =sum({$<Year={$(=max(Year))}>} Amount)

2) = sum(if(Year=yMax,Amount))

Need max year, max month and max day using set analysis

deepakk — Thu, 30 Dec 2010 06:54:53 GMT

Hi,

Logically both should display same value if yMax = Max(Year),

If in variable yMax you have to specify "=". If you miss it , it might cause a problem.

Need max year, max month and max day using set analysis

Thu, 30 Dec 2010 07:26:30 GMT

I have not missed , i used "=" sign for below variable.

yMax = max(Year)

Need max year, max month and max day using set analysis

deepakk — Thu, 30 Dec 2010 07:33:15 GMT

hi,

I made a sample test file and tested both the expression and its showing me same value.

Can you attach you application so that we can test it out.

Need max year, max month and max day using set analysis

Thu, 30 Dec 2010 08:09:05 GMT

Hi Deepak,

PFA.

Regards,

Antony

Need max year, max month and max day using set analysis

deepakk — Thu, 30 Dec 2010 11:18:37 GMT

Its little weird. Its working fine in my case.

Anyways I always prefer set analysis over If statement and the Set analysis part in your application working fine.

Best of Luck...

Need max year, max month and max day using set analysis

Thu, 30 Dec 2010 12:30:00 GMT

Hi,

Have you found Answer for max year, max month & max day using set analysis.

Need max year, max month and max day using set analysis

deepakk — Thu, 30 Dec 2010 13:04:15 GMT

I got it .... I got it...... I got it....

In you case the value is coming zero because the Max(Day) is coming as 31. so the maximum date becomes 31-dec-2010 and you have data only till 3rd Dec.

so use below expression

sum({$<Year = {$(=max(Year))},MonthNum = {$(=max(MonthNum))},Day = {$(= Day(max(Date(Date,'DD/MM/YYYY'))))}>}Amount)

For the if problem you have to use the if statement in below manner.

if(max(Year)=yMax,Sum(Amount))

I hope this will close the issue

Need max year, max month and max day using set analysis

Fri, 31 Dec 2010 07:54:14 GMT

Hi Deepak,

Can i have your mail id pls.

Need max year, max month and max day using set analysis

deepakk — Fri, 31 Dec 2010 07:55:56 GMT

kurup.d@gmail.com