topic Re: year,month issue? in QlikView

year,month issue?

narender123 — Tue, 09 Jul 2013 07:28:58 GMT

Hi Team,

i have a problem .I want sum of customs value of Max(Year ) and Max(Month) without any selection on year and month.

But it is not working by default with any selection.It is only working when i select the year then it takes the max(month)

of that year.

But i want it on without any selection on month and year.

expression i used is:

=sum({<Year={$(=Max(Year))},Month={$(=MaxString(Month))}>}CUSTOMS_VALUE)

please see my attachment file.

Thanks.

Re: year,month issue?

Tue, 09 Jul 2013 07:34:00 GMT

U can use the below expression

=sum({1<Year={$(=Max(Year))},Month={$(=MaxString(Month))}>}CUSTOMS_VALUE)

Regards,

Kabilan K.

Re: year,month issue?

er_mohit — Tue, 09 Jul 2013 07:42:48 GMT

sum({<Year={"$(=max(Year))"},Month={"=monthstart(max(Month))"}>}CUSTOMS_VALUE)

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 07:44:10 GMT

Thanks Kabilan.

But still same issue.

You can check my attachment

Thanks

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 07:58:00 GMT

Thanks Mohit.

But it takes the max year whole month data means up to this month data.

can you please tell me why me expression is not working becuase its the 1st time that this expressuion is not working here.

I have attached qvw .please see it.

Narender

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 08:33:34 GMT

Hi all,

Please tell me why my year and month are not working together when i didnt select both year and month.

As shown in my attachment.

?????

Thanks.

Re: year,month issue?

Tue, 09 Jul 2013 09:08:07 GMT

Hi Narender,

You could try an easier (in my opinion) approach by using a YearMonth field with a integer value. This field can be very useful in many other situations like creating a chart which shows a trend by YearMonth and you can sort also a Month dimension using this YearMonth field in the Sort tab of the chart object.

Create a YearMonth field like this:

LOAD DECLARATION_ID,
     RECEIPT_DATE,
     Year(RECEIPT_DATE) as Year,
     Month(RECEIPT_DATE) as Month,
     Year(RECEIPT_DATE) * 100 + num(Month(RECEIPT_DATE)) as YearMonth,
     Day(RECEIPT_DATE) as Day,
     CONSIGNOR_ID,
     CUSTOMS_VALUE
FROM
C:\3newreport_QlikviewProject\Development\Data\QVD\DEC_HEADER.qvd
(qvd) where Year(RECEIPT_DATE)<>2212;

and then use this expression:

=sum({<YearMonth={$(=Max(YearMonth))}>}CUSTOMS_VALUE)

Re: year,month issue?

tresB — Tue, 09 Jul 2013 09:12:43 GMT

Your Max (Year ) is 2013 and Maxstring(Month) is Dec, and there is no data for that period. please check.

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 09:14:13 GMT

Thanks Borja.

Some of my report based on max(day) or max(Day)-7 .So is working for max(day)part too??

Actually i have done all of these things well in my previous project where these expression running well but here these expression are not working .Could you tell me why this happens??

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 09:19:56 GMT

Thanks Tresko.

But when i write

Sum({<year={$(=max(year))},month={$(=maxstring(month))}>}customsvalue)

for this its will take 1st max(year)=2013 after that it should take Max(month) of Max(Year)2013 like jun according to max(month) in database.

In qlikview it will take the expression logically like this.

I have done this things in my previous project where it is running successfully.

but here not??

Narender

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 09:52:51 GMT

Hi Borja.

If i want to calculete max(Day)-7

=sum({<YearMonth={$(=Max(YearMonth))},Day={$(=Max(Day)-7)}>}CUSTOMS_VALUE)

it is not working.What i have to do???

Re: year,month issue?

Tue, 09 Jul 2013 10:14:57 GMT

Is Day a full date (20130709) or just the day number (9)?

If there is no data you will see 0 but it doesn't mean it is worng (like @tresesco mentioned here).

You could try a similar approach by creating a field with the full date in an integer format (e.g. 20130709 for today) in the LOAD statement and then in the expression just use that field in Set Analysis.

Re: year,month issue?

tresB — Tue, 09 Jul 2013 10:21:42 GMT

I guess you have to use P() in the SET.

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 10:47:21 GMT

My day is 9 .i can also place date like DD/MM/YYYY.

but not working

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 10:50:18 GMT

Hi Tresesco.

Thanks.

What do you mean

Where to write?

Re: year,month issue?

Tue, 09 Jul 2013 10:50:53 GMT

I still think you shoul use a full date field in the Set Analysis instead of a Day field with just the day number. It will make things easier.

Re: year,month issue?

tresB — Tue, 09 Jul 2013 11:00:50 GMT

Can you please let me know if the output of the following expression is okay for you or not, may be i can try then in other method?

=Sum(If(InMonth(RECEIPT_DATE,vMaxDate,0),RECEIPT_DATE))

Where vMaxDate= Max(RECIEPT_DATE)

Re: year,month issue?

narender123 — Tue, 09 Jul 2013 15:07:49 GMT

Yes its working.Showing right values.