topic Re: Expression in QlikView

Expression

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi all ,

can please soemoen correct my belwo expression ?

Iam trying to get the 2 years back data with the below expression ...

Please rectyfy

Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(LoadDate),-2))"}>}ID)

Thanks in advance

Re: Expression

jonathandienst — Thu, 16 Nov 2017 11:18:45 GMT

I assume Jira_LoadDate is a field. This will not be evaluated properly in the set expression. You probably need some sort of aggregation (Min or Max):

Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(Max(Jira_LoadDate)),-2))"}>}ID)

Re: Expression

jyothish8807 — Thu, 16 Nov 2017 11:20:28 GMT

Hi John,

Try like this:

Create two variables:

1. vMaxYear-2 : = date(max(LoadDate),'YYYY') - 2

2. vMaxYear : =date(max(Jira_LoadDate),'YYYY')

Make sure the Purchase Date is also in 'YYYY' format. Also check the output of the variable once, since i do not know the formatas you actually have these dates.

Count({<[Dep]={'A'},PurchasedDate={">=$(vMaxYear-2) <=$(vMaxYear )"}>}ID)

Br,

Re: Expression

its_anandrjs — Thu, 16 Nov 2017 11:23:29 GMT

You can try this as well by Addmonths by take expression to 2 years back.

=Count({<[Dep]={'A'},PurchasedDate={">=$(=AddMonths(Max(LoadDate),-24)) <=$(=AddMonths( Max(Jira_LoadDate)),-24))"}>}ID)

Re: Expression

smilingjohn — Thu, 16 Nov 2017 11:33:49 GMT

Hi Jonathan

sorry..

the expression is like this

Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(LoadDate),-2))"}>}ID)

the field is Loadate ...

by mistake i type Jira_loaddate

Re: Expression

smilingjohn — Thu, 16 Nov 2017 11:34:42 GMT

Hi ANand sorry the expression is like this

Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(LoadDate),-2))"}>}ID)

the field is Loadate ...

by mistake i type Jira_loaddate

Re: Expression

PrashantSangle — Thu, 16 Nov 2017 11:46:16 GMT

did you try jonathan suggestion

you must max(LoadDate)

Regards

Re: Expression

its_anandrjs — Thu, 16 Nov 2017 11:48:00 GMT

What O/P you get

May be this are you have to try

=Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd( Max(LoadDate),-2)))"}>}ID)

=Count({<[Dep]={'A'},PurchasedDate={">=$(=Yearstart( Addmonths(Max(LoadDate),-24))) <=$(=YearEnd( Addmonths( Max(LoadDate),-24)) ))"}>}ID)

Re: Expression

sasiparupudi1 — Thu, 16 Nov 2017 12:00:21 GMT

Count({<[Dep]={'A'},PurchasedDate={">=$(=YearStart(Max(LoadDate),-2))<=$(=YearEnd(Max(LoadDate))))"}>}ID)

Make sure that the format of the PurchasedDate and LoadDate is the same

Re: Expression

jonathandienst — Thu, 16 Nov 2017 12:07:40 GMT

>>Hi Jonathan

sorry..

the expression is like this ....

Just change the field name and leave the rest of the expression.

Re: Expression

sunny_talwar — Thu, 16 Nov 2017 12:19:28 GMT

Try one of these

Count({<[Dep]={'A'}, PurchasedDate={">=$(=Yearstart(Max(LoadDate), -2))<=$(=Date(YearEnd(Max(LoadDate)), -2)))"}>}ID)

or this

Count({<[Dep]={'A'}, PurchasedDate={">=$(=Date(Yearstart(Max(LoadDate),-2), 'DateFieldFormatHere'))<=$(=Date(YearEnd(Max(LoadDate)),-2), 'DateFieldFormatHere'))"}>} ID)

Make sure to use the correct format of your PurchasedDate within the set modifier.

Also, read here:

Dates in Set Analysis

Re: Expression

smilingjohn — Thu, 16 Nov 2017 13:22:06 GMT

Hi sunny

Iam using the below the below expression ..

but somewhere i feel This is not correct ..PLease correct me

Pick(Dim,

count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate)))<=$(=Max(LoadDate))"}>} Key),

Count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(Today(),-2))"}>} Key)/12,

Count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-1))<=$(=YearEnd(Today(),-1))"}>} Key)/12,

count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate)))<= $(=Max(LoadDate))" }>}Key)/12))

Re: Expression

smilingjohn — Thu, 16 Nov 2017 13:43:07 GMT

In dimension iam using this ...

=Pick(Dim,MonthName(Created),$(vloadyear)-2,$(vloadyear)-1,$(vloadyear))

vloadyear= year(LoadDate)

I willl be using the loadDate as a filter ..

When i select any date form load date the data should display accordingly....

if i select 2016 from loadate it should go back and show avgof 2014 and avg 0f 2015,2016

as shown in the screenshot

Re: Expression

sunny_talwar — Thu, 16 Nov 2017 14:05:57 GMT

I guess I understand what you want, but where is the problem? Which part of the expression not working or the whole expression not working?

Re: Expression

smilingjohn — Thu, 16 Nov 2017 14:36:36 GMT

Hi sunny .

Thanks ..

This expression works fine ...the only doubt I have to confirm from you for the below expression is that ....if you look iam using

Yearend(Today() .....will this work fine ....even in the future for 2018 ...

Pick(Dim,

count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate)))<=$(=Max(LoadDate))"}>} Key),

Count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-2))<=$(=YearEnd(Today(),-2))"}>} Key)/12,

Count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate),-1))<=$(=YearEnd(Today(),-1))"}>} Key)/12,

count({<[Department]={'A'},PurchasedDate={">=$(=Yearstart(Max(LoadDate)))<= $(=Max(LoadDate))" }>}Key)/12))

Re: Expression

sunny_talwar — Thu, 16 Nov 2017 14:39:19 GMT

I think it should work... I don't see why it wouldn't

Re: Expression

greend21 — Thu, 16 Nov 2017 16:27:30 GMT

Maybe try an If statement in the load that will give you all dates you want as a 1. The below should give you dates between two years and ago the end of the current Load Date month:

If(yearstart(AddYears(today(),-2))<=LoadDate and (MonthEnd(today())>=LoadDate,1,0) as YearFlag

Then use YearFlag in your set analysis.

Count({$<YearFlag={1}, Dep={'A'}>}ID)