topic Re: Expression in QlikView

Expression

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

Hi all ,

All my requirement was to show the Average of 2015 and 2016 and current years count for all the 12 months.

It worked for me perfectly when i used the dummy data .

And when iam using the real time data it is not working ....

Below is the screenshot of the working table .

Please find the attachment of the QVW file which has the real time data .

Can someone please suggest me on this

regards

sunny_talwar — Tue, 03 Oct 2017 11:28:40 GMT

Revenue is a text field... how can find it's average?

Also, try this as your dimension

=Pick(Dim,MonthName(Created),'AVG 2016','AVG 2015')

sunny_talwar — Tue, 03 Oct 2017 11:31:07 GMT

I also suggest creating a MonthYear field in the script like this

Data:

LOAD JIRA_KEY_ID as Revenue,

Timestamp(Timestamp#(left(CREATE_DATE,18),'DD-MMM-YYYY hh.mm.ss'),'DD-MM-YYYY') as Created,

Date(MonthStart(TimeStamp#(Left(CREATE_DATE,18),'DD-MMM-YYYY hh.mm.ss')), 'MMM YYYY') as MonthYear

FROM

(ooxml, embedded labels, table is [Export Worksheet]);

smilingjohn — Tue, 03 Oct 2017 11:34:18 GMT

Hi Sunny ,

Yes it is texy field

they are considered to be thr ID's thereofre we are counting it .

sunny_talwar — Tue, 03 Oct 2017 11:36:23 GMT

But you are averaging them also?

Pick(Dim,

count({<Created={">=$(=YearStart(Today()))<=$(=YearEnd(Today()))"}>} Revenue),

Avg({<Created={">=$(=YearStart(Today(),-1))<=$(=YearEnd(Today(),-1))"}>} Revenue),

Avg({<Created={">=$(=YearStart(Today(),-2))<=$(=YearEnd(Today(),-2))"}>} Revenue))

sunny_talwar — Tue, 03 Oct 2017 11:37:42 GMT

Are you looking for this may be

Pick(Dim,

count({<Created={">=$(=YearStart(Today()))<=$(=YearEnd(Today()))"}>} Revenue),

Count({<Created={">=$(=YearStart(Today(),-1))<=$(=YearEnd(Today(),-1))"}>} Revenue)/12,

Count({<Created={">=$(=YearStart(Today(),-2))<=$(=YearEnd(Today(),-2))"}>} Revenue)/12)

smilingjohn — Tue, 03 Oct 2017 11:44:05 GMT

Yes Bro .

You are always Awesome

Thansk a lot