topic Re: Year-1 / Month -1 in QlikView

Year-1 / Month -1

master_student — Tue, 24 Oct 2017 08:21:17 GMT

Hello Guys,

I hope all is good.

I need to get the value of month -1 (of the selected month), YEAR-1 of the expression bellow :

=num(sum( [Active Base] ) ,'# ##0')

My fields are month of date, year of date

Thanks a lot

Re: Year-1 / Month -1

zhadrakas — Tue, 24 Oct 2017 09:01:58 GMT

Hello,

try like this

num(Sum({$<Year={"=max(Year)-1"}, Month={"=max(Month)-1"}>}[Active Base]) , '# ##0')

regards

tim

Re: Year-1 / Month -1

prma7799 — Tue, 24 Oct 2017 09:07:54 GMT

Try like this

=Num(Sum({$<[Posting Date]={"=max(Year([Posting Date]))-1"}, [Posting Date]={"=max(Month([Posting Date]))-1"}>}[Gross Sales Amount]) , '###0')

Re: Year-1 / Month -1

techvarun — Tue, 24 Oct 2017 09:10:02 GMT

Num((sum({<[month of date]={$(=Month(TODAY())-1)}>} [Active Base]) + sum({<[Year of date]={$(=Month(TODAY())-1)}>} [Active Base])),'# ##0')

Re: Year-1 / Month -1

its_anandrjs — Tue, 24 Oct 2017 09:15:40 GMT

Try this way in Month use the Datefield

=num(sum( {<Year={"$(=Max(Year)-1)"}, Month={"$(=Max(Month(DateField))-1)"} >} [Active Base] ) ,'# ##0')

Re: Year-1 / Month -1

its_anandrjs — Tue, 24 Oct 2017 09:42:18 GMT

One question how many date fields you have.

Re: Year-1 / Month -1

Anil_Babu_Samineni — Tue, 24 Oct 2017 10:04:52 GMT

This one works, but you need top assign dollar symbol

Re: Year-1 / Month -1

Anil_Babu_Samineni — Tue, 24 Oct 2017 10:06:32 GMT

You can't write like this, because Set analysis explorer miffed with only one field for one time.

Re: Year-1 / Month -1

prma7799 — Tue, 24 Oct 2017 10:12:58 GMT

Hi Anil,

I appreciate your suggestion but could you please explain more. I didn't understand ...

Thanks

Re: Year-1 / Month -1

Anil_Babu_Samineni — Tue, 24 Oct 2017 10:16:51 GMT

The thing is simple, you added [posting Date] as 2 times In the left hand side of degrade expression. That means qlikview doesn't capture for which option need to calculate so the only option is either used year and month calculation from field called date field. Does it likable?

Re: Year-1 / Month -1

vikasdesai — Tue, 24 Oct 2017 10:21:43 GMT

Hello

Use Following Expression

For Year-1

Sum({<Year={$(=max(Year)-1)>}[FieldName])

For Month-1

Sum({<Month={$(=max(Month)-1)>}[FieldName])

Regards

Vikas

Re: Year-1 / Month -1

its_anandrjs — Tue, 24 Oct 2017 13:28:26 GMT

Better you can try this and change the format of the month field because as you posted you have only Year and Month fields for date fields

=Num( Sum({<Year={"$(=Max(Year)-1)"},Month={"$(=Date(Date#(Max(Month)-1,'MM'),'MMM'))"} >} [Active Base] ),'# ##0')