topic Re: Need coming month value in QlikView

Need coming month value

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

Dear All,

I have one query that is I want full quarter value when i select Q4 ...

PFA....

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 10:41:25 GMT

How are you coming up with value of 15,893.13? Can you explain the logic here?

Re: Need coming month value

Anonymous — Thu, 02 Feb 2017 10:48:41 GMT

I have excel for target values and I want jan17 feb17 and mar17 values.

Jan-17	Feb-17	Mar-17
1805.00	2031.00	1692.00
272.88	265.50	236.00
247.50	220.00	192.50
880.00	770.00	825.00
155.89	166.29	145.50
72.86	77.71	68.00
128.00	112.00	112.00
592.00	592.00	592.00
621.00	584.00	621.00
289.00	289.00	275.00
247.50	275.00	330.00
45.00	35.00	30.00

Thanks

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 10:50:05 GMT

I don't know where you loading those values, but the max month in your dashboard is 01/01/2017.... there is no Feb-17 and Mar-17 data in your application to display it

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 10:51:42 GMT

Are you saying that you have additional data for Target and would like to bring that into the application? Can you share the ABC.qvd and the Target Excel file?

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 10:58:49 GMT

And ABC.qvd?

Re: Need coming month value

Anonymous — Thu, 02 Feb 2017 11:01:54 GMT

PFA

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 11:52:29 GMT

Checking now... left because I was leaving for work

Re: Need coming month value

Anil_Babu_Samineni — Thu, 02 Feb 2017 12:01:45 GMT

From that qvw, You are showing one Expected values, How that value is coming up

And, I am not fully understnad your intend to do.

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 12:10:17 GMT

Where did the Target Excel file go?

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 12:18:59 GMT

Have you used this Excel to create the ABC.qvd?

Re: Need coming month value

Anonymous — Thu, 02 Feb 2017 12:36:40 GMT

yes....

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 12:42:13 GMT

I think you are skipping the Feb and Mar data in the process... can you share the script you use to create ABC.qvd?

Re: Need coming month value

Anonymous — Thu, 02 Feb 2017 13:01:46 GMT

Actually Sunny I was wrong and I had gave you wrong file ...

Sorry for that ....

Please find the exact excel file and application.

I have converted the attached excel file into qvd and process it in live application .

Thanks

Re: Need coming month value

sunny_talwar — Thu, 02 Feb 2017 13:08:47 GMT

What is vMaxDate?

Fact_DivisionTarget:

LOAD SG_ID,

date(DivTarDate,'DD/MM/YYYY') as DivTarDate,

DivAnnualTgt,

DivMonthlyTgt,

finyr,

year,

flag

FROM

[test.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where DivTarDate <= date('$(vMaxDate)','DD/MM/YYYY') ;

DROP TABLE Fact_DivisionTarget;

ABC:

LOAD

AUTONUMBER([SG_ID] & ' _' & Date(floor(DivTarDate),'DD/MM/YYYY')) As %DIVTAR_KEY,

DivTarDate,

[SG_ID],

DivAnnualTgt ,

DivMonthlyTgt ,

year(DivTarDate) as Year ,

month(DivTarDate) as Month ,

YearName(DivTarDate,0,4) AS FinancialYear,

'Q' & alt(if(Month(DivTarDate)<4,4),if(Month(DivTarDate)<7,1),if(Month(DivTarDate)<10,2),3) AS Quarter

FROM

[Target.qvd]

(qvd);

Re: Need coming month value

Anonymous — Fri, 03 Feb 2017 04:00:21 GMT

vMaxDate = 02/01/2017

Re: Need coming month value

sunny_talwar — Fri, 03 Feb 2017 09:13:51 GMT

I am a little confused....

1) why do you load data from Excel and Target.qvd? Is one loading past data (Excel) and one loading future data (qvd)?

2) If what I wrote above is not true and the Excel loads the future data as well.... why are you restricting it to load only before today's data when you want to see Feb and Mar in your table??

Please enlighten me

Re: Need coming month value

Anonymous — Fri, 03 Feb 2017 09:37:26 GMT

Hi Sunny,

I have load excel into my extractor to convert into qvd and the it load into main file.

I got target file form user so need to load all month target.

My requirement is if user select 2016-17 then he can see total target and quarter wise target

e.g

Total Target Q1Tar Q2Tar Q3Tar Q4Tar

100 25 25 25 25

but i am getting

Total Target Q1Tar Q2Tar Q3Tar Q4Tar

100 25 25 25 15

rest of 10 is sum of feb and march is not coming

thanks

Re: Need coming month value

sunny_talwar — Fri, 03 Feb 2017 09:38:54 GMT

Can you try to load this without the where statement?

Fact_DivisionTarget:

LOAD SG_ID,

date(DivTarDate,'DD/MM/YYYY') as DivTarDate,

DivAnnualTgt,

DivMonthlyTgt,

finyr,

year,

flag

FROM

[test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Where DivTarDate <= date('$(vMaxDate)','DD/MM/YYYY') ;