topic Re: Discussion for calculating calendar days in QlikView

Discussion for calculating calendar days

bushpalaamarnat — Tue, 20 Feb 2018 17:29:29 GMT

Hi Every one,

I got a project to do for a plant, they have a challenge in calculating the calendar days based on the data which they get from the application. Some products are set on 5 days week calendar days and some on 6 or 7.

how ever, when it come to days forward cover (DFC) the data does not fit into the bracket of 5, 6 or 7 days.

attached the data set for reference. in excel they are subtracting the last week's data of DFC by this week's data if most of the week the dfc difference is coming to either 5 or 6 or 7, they put it as respective calender days. If they are any outliner, they ignore.

Is there a way to replicate that in qlikview?

I tried using Mode and range max but did not get calender days for that specific product and location?

This is my current SCRIPT.

Table:

LOAD

Week,

Material,

DFC,

[Customer Loc],

Material & [Customer Loc] as %keymaster,

[Customer Zone]

FROM

ICR_Load.qvd](qvd);

Table2:

NoConcatenate

LOAD *,

Previous(DFC) as tet1,

num(fabs( If([Customer Zone] ='EUROPE' OR [Customer Zone] = 'NORTH AMERICA',5,

If(DFC='-',0,

If(Material = Previous(Material) and [Customer Loc] = Previous([Customer Loc]),

[DFC]- Previous(DFC), 0)))),'##0') as Days_Differ

Resident Table

Order By Material, [Customer Loc], Week desc;

DROP Table Table;

FinalTable:

NoConcatenate

LOAD

RangeMax(Days_Differ,7) as CalenderDays

Resident Table2

Order By Material, [Customer Loc], Week desc;

DROP Table Table2;

Re: Discussion for calculating calendar days

bushpalaamarnat — Tue, 20 Feb 2018 17:33:17 GMT

Sorry every one, i missed the attachment.

Re: Discussion for calculating calendar days

isingh30 — Tue, 20 Feb 2018 18:44:21 GMT

Please try Master Calender. Check the below link.

The Master Calendar

Thanks.

Re: Discussion for calculating calendar days

bushpalaamarnat — Tue, 20 Feb 2018 20:06:10 GMT

‌tthank you for you response. I guess am not clear. My apologies.

My data set does not have date feature. And the plant is in need to calculate days forward cover.

Re: Discussion for calculating calendar days

isingh30 — Tue, 20 Feb 2018 20:11:14 GMT

Check this -

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions/networkdays.htm

Thanks.

Re: Discussion for calculating calendar days

bushpalaamarnat — Wed, 21 Feb 2018 05:07:07 GMT

‌well, thank you again for response. Am aware about the network days concept. UUnfortunately, it does not support the need.

Re: Discussion for calculating calendar days

Digvijay_Singh — Wed, 21 Feb 2018 05:45:29 GMT

Can you clarify what exactly you need after this, I tried to replicate your script in Qlik, The sample doesn't have customer zone so I have ignored it as of now.

Re: Discussion for calculating calendar days

bushpalaamarnat — Wed, 21 Feb 2018 07:08:21 GMT

‌sir, thank you for replicating in Qlik. Could you please share the replication model. That will help.

Re: Discussion for calculating calendar days

bushpalaamarnat — Wed, 21 Feb 2018 12:36:17 GMT

Sir, thank you for the model, i have put a table. in which you will seek a Material and customer location, for that material and location the days_differ column has the most repeating value to show in calendar days. In this case, if 7 is repeating then calendar days is 7. again, if the value is more then 7 that is repeating maximum times, it should say zero.

In a week they can have only 7 days.

Thank you very much Sir.

Re: Discussion for calculating calendar days

Digvijay_Singh — Wed, 21 Feb 2018 16:41:56 GMT

I don't have license to access QVW, so cannot check the attachment but based on your explanation I tried to update the model. I don't see any case in your sample where frequency of Day Differ is more than 7, can you try this attached model with the Material,Location when Day Differ of >7 is most frequent.

Re: Discussion for calculating calendar days

bushpalaamarnat — Wed, 21 Feb 2018 21:04:39 GMT

Sir, attached an image of how the data is appearing. now in the calendar days for all those fields in Differ_days showing great > 7 should 7. in the same way if for a set of data with a material code and customer location combination has max repeating value as 5, then in calendar days it should appear as 5 for all those materials.

Re: Discussion for calculating calendar days

Digvijay_Singh — Wed, 21 Feb 2018 21:38:39 GMT

I thought the latest change I shared, shows this -

1. If for material code and customer location combination, 7 or below repeats maximum times then calendar days = the maximum count ( I mean if 7 repeats maximum time then 7, if 5 repeats maximum time then 5)

2. If for material code and customer location combination, the maximum repeated number is >7, then show zero in calendar days.

May be I am missing something but in the sample you shared, I see many 1's and 2's are maximum repeat for few combination other than 7 but don't see any 5s or 6s which are maximu repeat for some combination.

Let me know if the last shared file is not producing above results.

Re: Discussion for calculating calendar days

bushpalaamarnat — Thu, 22 Feb 2018 07:55:50 GMT

‌Sir, exactly correct. Anything below 7 repeats remain same. Anything above 7 should be 0. If it is 1 then 1 , 2will be 2.

Re: Discussion for calculating calendar days

Digvijay_Singh — Thu, 22 Feb 2018 15:19:07 GMT

I think this is what the script I shared should be doing. Please share any combination where it is not doing.

Re: Discussion for calculating calendar days

bushpalaamarnat — Sat, 24 Feb 2018 19:56:09 GMT

Sir, the attachment show's how i can see the qvw that you loaded.

Unfortunately, it does not show the table which you have in the screen shot. Could you share the calculation or the formula\expression what your are giving.

Also, for a the table, it should appear like. I don't need this column "Countif(D:D,7)" as in this example the 7 is repeating most times for that material and location, hence it is 7 days in calendar .

Week	Material	Customer Loc	Days_Differ	COUNTIF(D:D,7)	CalenderDays
W06-18	7890760	ZAD	15	36	7
W07-18	7890760	ZAD	7	36	7
W08-18	7890760	ZAD	7	36	7
W09-18	7890760	ZAD	7	36	7
W10-18	7890760	ZAD	19	36	7

Re: Discussion for calculating calendar days

Digvijay_Singh — Sat, 24 Feb 2018 21:10:23 GMT

I added the Straight table as you needed.

Re: Discussion for calculating calendar days

bushpalaamarnat — Tue, 27 Feb 2018 00:38:15 GMT

Thank you very much Sir. it did work out. great help.