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]

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.

[Digvijay_Singh]

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.

[bushpalaamarnat]

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

[Digvijay_Singh]

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

[bushpalaamarnat]

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

[Digvijay_Singh · Accepted solution]

I added the Straight table as you needed.

[bushpalaamarnat]

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