topic Re: YTD Issue in QlikView

YTD Issue

bharatkishore — Thu, 08 Mar 2018 12:51:17 GMT

Hi All,

I have a qvd with following fields

YearMonth,

Calendar_Year,

Plant,

BUEqui,

Strategic_Flag,

Country,

Contract_Type,

Part_Type,

Fiscal_Month,

ITM_IB,

ITM_Consumption

and to get YTD logic i have given the below condition:

FinalTable:

LOAD*,

If(BUEqui = Previous(BUEqui) and Plant = Previous(Plant) and Calendar_Year = Previous(Calendar_Year) , RangeSum(ITM_Consumption, Peek('YTD_Consumption')), ITM_Consumption) as YTD_Consumption

Resident QV_intermediate_temp5

order by BUEqui,Plant,YearMonth ;

DROP Table QV_intermediate_temp5;

Below image for reference:

If you see my YTD_cons for Jan is fine but for feb instead of 1934+6913 =8847 i am getting 176974.

Can you please help where i am doing wrong.

Attached app for reference.

Thanks,

Bharat

Re: YTD Issue

sunny_talwar — Thu, 08 Mar 2018 12:59:36 GMT

There are multiple rows with same dimension values... may be you want to Sum them before you calculate YTD_Consumption?

Re: YTD Issue

bharatkishore — Thu, 08 Mar 2018 13:00:32 GMT

Yes Sunny Bhai.. Is there anything wrong in the data..

Re: YTD Issue

sunny_talwar — Thu, 08 Mar 2018 13:02:36 GMT

wrong with data... I don't know... wrong with way script is running... may be (because you are not getting the right output)

Re: YTD Issue

bharatkishore — Thu, 08 Mar 2018 13:03:26 GMT

Yes, Can you please help how can i get it..

Re: YTD Issue

sunny_talwar — Thu, 08 Mar 2018 13:06:25 GMT

can you share your qvd?

Re: YTD Issue

bharatkishore — Thu, 08 Mar 2018 13:08:44 GMT

Please find the attached qvd

Re: YTD Issue

bharatkishore — Thu, 08 Mar 2018 13:09:39 GMT

In case if you got T9 qvd please don't use .. kindly use T10 qvd i have shared..

Please let me know if you need anything more..

Re: YTD Issue

sunny_talwar — Thu, 08 Mar 2018 13:11:11 GMT

Check attached

Re: YTD Issue

bharatkishore — Thu, 08 Mar 2018 13:18:29 GMT

Thank You Sunny.

But if i select 4 months the value is getting wrong. Please find the attached app.

Below image for reference;

Instead of 12350 i am getting 14822 and if i change any BUEQUI also the values are not getting same.

Can you please have a look..

Re: YTD Issue

sunny_talwar — Thu, 08 Mar 2018 13:23:53 GMT

Check now

Re: YTD Issue

bharatkishore — Thu, 08 Mar 2018 13:27:37 GMT

Sunny Bhai.. Thank you so much. Thanks a lot..

One last question can you please tell me why you have given these two steps:

AutoNumber(Plant&BUEqui&Strategic_Flag&Country&Contract_Type&Part_Type&ITM_IB&Calendar_Year) as Key,

Sum(ITM_Consumption) as ITM_Consumption

why we did sum(ITM_Consumption)..

Please..Kindly wanted to know.

Re: YTD Issue

sunny_talwar — Thu, 08 Mar 2018 13:30:38 GMT

1) Created Key as it helped make the if condition and Order by easier... you don't have to create the Key... but then you will have to add all the fields from the Key into your if condition and Order By

2) Sum because like I mentioned before... you have multiple rows for each combination of Key making Previous function not work the way you would expect it to work

Re: YTD Issue

bharatkishore — Thu, 08 Mar 2018 13:32:19 GMT

Thanks a lot Sunny Bhai. Thank you so much. I know you don't like to say these many times.. But thank you so much..