topic Null data repeats the above value in QlikView

Null data repeats the above value

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

Hi Community,

I'm facing the one issue, Actually my data not properly.

Budget data they were given to me, Month wise. (Jan to Dec)

Have converted to date level, my challenge is compare week wise data.

suppose WEEK1 = Jan Budget/Number of working days in a week.

Suppose My Jan data =200

=200/5 (working days)

=40

See the below image,Zero values have to fill the above value...

Note: JAN month all Weeks should be same amount

Please help me...

PFA..

Re: Null data repeats the above value

Anil_Babu_Samineni — Mon, 14 May 2018 14:01:20 GMT

Can you try this in script Fiscal Year and you will get automatic.

Re: Null data repeats the above value

sunny_talwar — Mon, 14 May 2018 14:08:54 GMT

May be this

Sum(Aggr(If(Date#(Month_Year, 'MMM - YYYY') = Max(TOTAL <Year, Week> Date#(Month_Year, 'MMM - YYYY')), Sum(TOTAL <Month_Year>SalesAmount)), Year, Week, Month_Year))/5

Re: Null data repeats the above value

paulwalker — Mon, 14 May 2018 14:10:22 GMT

Hi Anil,

I have generated Dates, but i want repeat the value.

Suppose i have date 01-01-2018 value 100 and Weeks (Week1, week2, week3, week4).

for JAN all week value should be the same...

I have tried with SUm(Total Amount), but this giving full total of the months.

Re: Null data repeats the above value

sunny_talwar — Mon, 14 May 2018 14:10:39 GMT

Or this

Sum(Aggr(If(Date#(Month_Year, 'MMM - YYYY') = Max(TOTAL <Year, Week> Date#(Month_Year, 'MMM - YYYY')), Sum(TOTAL <Month_Year>SalesAmount)), Year, Week, Month_Year))/

NetWorkDays(MIN(Common_Date), MAX(Common_Date))

Re: Null data repeats the above value

paulwalker — Mon, 14 May 2018 14:30:35 GMT

Hi Sunny,

This is working fine, but if i make more than one Month selection not working properly..

IF i select JAN and FEB

taking like ( (JAN+FEB)/no of working days in a month) * No.of working days in a week

Please help

Re: Null data repeats the above value

sunny_talwar — Mon, 14 May 2018 14:32:47 GMT

paulwalker‌ - can you provide the exact numbers you are looking to get with and without selection?

Re: Null data repeats the above value

paulwalker — Mon, 14 May 2018 15:00:13 GMT

Sorry, Let me clear..

IF i make selection JAN and FEB, by default its taking 20 working days. (Jan working days should be 23 and Feb working days should be 20)

Jan weeks (Week 1 to 5) that should be Divide by 23 working days

Feb weeks (Week 1 to 5) that should be Divide by 20 kworking days

Hope it clear...

Re: Null data repeats the above value

paulwalker — Tue, 15 May 2018 10:57:54 GMT

Hi Sunny,

Thanks for your help..

This expression working fine for me...

(Sum(Aggr(If(Date#(Month_Year, 'MMM - YYYY') = Max(TOTAL <Year, Week> Date#(Month_Year, 'MMM - YYYY')), Sum(TOTAL <Month_Year>SalesAmount)), Year, Week, Month_Year))

Concat(DISTINCT NetWorkDays(MonthStart(MakeDate(Year, Month)), MonthEnd(MakeDate(Year, Month))), ', '))

NetWorkDays(MIN(Common_Date), MAX(Common_Date))

But I'm facing one problem, I'm not able to exclude Holidays..

Please can you help on this...

HolidayDate, Name

2018-01-01, New Years Day

2018-03-30, Good Friday

2018-04-02, Easter Monday

I want to exclude these one's...

Re: Null data repeats the above value

sunny_talwar — Tue, 15 May 2018 11:03:52 GMT

Why not? What issues are you running into? May be create a variable with these dates and pass it to the NetWorkDays() function