Discussion for calculating calendar days

[bushpalaamarnat]

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;

[bushpalaamarnat]

Sorry every one, i missed the attachment.

[isingh30]

Please try Master Calender. Check the below link.

The Master Calendar

Thanks.

[bushpalaamarnat]

‌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.

[isingh30]

Check this -

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

Thanks.

[bushpalaamarnat]

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

[Digvijay_Singh]

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.

[bushpalaamarnat]

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

[bushpalaamarnat]

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.

[Digvijay_Singh]

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.