topic Re: Ageing report mismatch in QlikView

Ageing report mismatch

prma7799 — Tue, 04 Jul 2017 07:10:47 GMT

Dear All,

I have created one ageing report in that I am getting all data according to user but I am getting proper ageing for one document "EXP/380/16-17" which is also highlighted in attachment.

Please check .

Re: Ageing report mismatch

RonaldDoes — Tue, 04 Jul 2017 07:20:01 GMT

Hi PM,

The due date for EXP/380/16-17 is 28-1-2017 so it would be expected to be in the 1-180 bucket, right?

Re: Ageing report mismatch

prma7799 — Tue, 04 Jul 2017 07:22:03 GMT

Yes..

Can anyone tell why this document is not coming in 1-180 Days ageing.

Thanks

Re: Ageing report mismatch

ankur_abhishek — Tue, 04 Jul 2017 08:18:05 GMT

HI PM,

As per my analysis you have used expression for 1-180:

sum({<Due_BucketNEW={'1-180' ,'Not Due'} ,[Posting Date]={'>=$(=Date(vStart))<=$(=Date(vEnd))'}>}[Amount (LCY)])

& if I filter the raw data by Document No. EXP/380/16-17

then

Column: Due_BucketNEW = 1-180 which is satisfying the above condition. This is the reason for your issue.

Re: Ageing report mismatch

prma7799 — Tue, 04 Jul 2017 09:02:35 GMT

Hi Ankur / tresesco‌

But if you check expected output excel in that posting date is 31-08-16 for that document .

I am selection date range from to 30.4.2017

My ageing logic is

30/4/2017 31/3/2017 28/2/2017 31/1/2017 31/12/2016 30/11/2016 >>>>>>>> 1-180 Days

31/10/2016 30/09/2016 31/8/2016 31/7/2016 30/6/2016 30/5/2016 30/4/2016 >>>>>>> Above 180 days

So according to this it has to come in "Above 180 days " ageing column

Thanks

Re: Ageing report mismatch

antoniotiman — Tue, 04 Jul 2017 09:24:53 GMT

Hi,

Your Expression is

sum({<Due_BucketNEW={'1-180' ,'Not Due'} ,[Posting Date]={'>=$(=Date(vStart))<=$(=Date(vEnd))'}>}[Amount (LCY)])

and

Regards,

Antonio

Re: Ageing report mismatch

prma7799 — Tue, 04 Jul 2017 09:30:00 GMT

Hi Antonio ,

I know this What I want is this document should display in >180 Column

Re: Ageing report mismatch

tresB — Tue, 04 Jul 2017 09:41:20 GMT

Your dimension Due_BucketNEW has been calculated against [Due Date] in the script. That makes the calculation like :Today()-42763=157 and this puts it under 1-180 bucket. You probably might want to recalculate the new bucketing against [Posting Date]?

Re: Ageing report mismatch

prma7799 — Tue, 04 Jul 2017 09:45:54 GMT

I already created bucket using posting date "PostingDate_Bucket" but when i used PostingDate_Bucket then my report will not match with user report that is expected output excel .

Actually the logic is base posting date only but i am not able to get it.

Re: Ageing report mismatch

antoniotiman — Tue, 04 Jul 2017 09:46:15 GMT

What about Expression Like this

If(Today()- Min([Posting Date]) > 180,Sum({<[Posting Date]={'>=$(=Date(vStart))<=$(=Date(vEnd))'}>}[Amount (LCY)]))

Re: Ageing report mismatch

prma7799 — Tue, 04 Jul 2017 09:54:07 GMT

if you use PostingDate_Bucket then it bifurcating values in both ageing for all document ...

Please give some logic or idea that how to achieve the same .

PFA for sample data without bucketing fields

Thanks

Re: Ageing report mismatch

prma7799 — Tue, 04 Jul 2017 10:04:10 GMT

Hi Antonio ,

using your expression the EXP/687/16-17 will come in >180 but in actual it is in 1-180.

Re: Ageing report mismatch

antoniotiman — Tue, 04 Jul 2017 10:04:26 GMT

Re: Ageing report mismatch

prma7799 — Tue, 04 Jul 2017 10:12:55 GMT

What about this???

Re: Ageing report mismatch

antoniotiman — Tue, 04 Jul 2017 10:16:31 GMT

Min(Posting Date) for this is 31-12-2016