topic Re: Set analysis range in QlikView

Set analysis range

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

Struggling with the right syntax for a sum of units;

I need to sum the units from the start of the month and 59 days forward. Date variable in the chart/table looks like this;

Expression SO3 is;

sum({1<Date= {">=$(=vMonthStartDate)<=$(='30/10/2016')"}> } [Units])

this seems to break up the calculated values by [MonthYear]. The result i need is a value for Sep-16 that is the sum of the [Units] between 1/9/2016 and 30/10/2016 - 461648 (239995+2211653) units displayed in place of the 239995 number.

Not quite sure where i am going wrong.

Thanks,

Chris

Re: Set analysis range

tresB — Thu, 25 Aug 2016 05:00:30 GMT

It could be date format issue. Try like:

sum({1<Date= {">=$(=Date(vMonthStartDate,'DD/MM/YYYY'))<=$(='30/10/2016')"}> } [Units])

Re: Set analysis range

chris_munns — Thu, 25 Aug 2016 05:09:12 GMT

Hi Amit,

Thanks for your thoughts, unfortunately the same result.

I think if the formatting wasn't quite right, SO3 would show '-'.

Cheers,
Chris

Re: Set analysis range

qlikviewwizard — Thu, 25 Aug 2016 07:28:40 GMT

Hi Try like this:

sum({1<Date= {">=$(=vMonthStartDate)<=$(='10/30/2016')"}> } [Units])

Re: Set analysis range

tresB — Thu, 25 Aug 2016 08:35:34 GMT

Have you checked the output of your variable? Could you post a sample qvw?

Re: Set analysis range

Anil_Babu_Samineni — Thu, 25 Aug 2016 08:40:43 GMT

Have you checked this

Your SET date should same as DD/MM/YYYY

vMonthStartDate == ??????

sum({1<Date= {">=$(=vMonthStartDate)<='30/10/2016'"}> } [Units])

Re: Set analysis range

antoniotiman — Thu, 25 Aug 2016 09:14:12 GMT

Hi Chris,

how about Below() function like this

RangeSum(Below(Sum(Units),0,2))

Re: Set analysis range

sunny_talwar — Thu, 25 Aug 2016 09:24:23 GMT

Can you try this:

Sum({1<Date= {"$(='>=' & Date(MonthStart(Today(), 1), 'D/MM/YYYY') & '<=' & Date(MonthStart(Today(), 1) + 59, 'D/MM/YYYY'))"}>} [Units])

Re: Set analysis range

chris_munns — Thu, 25 Aug 2016 11:08:30 GMT

hi all,

Thanks for the feedback, here is the update from your suggestions;

SO4 (Antonio Mancini) =RangeSum(Below(Sum([Units]),0,2))

SO5 (Sunny T) = Sum({1<Date= {"$(='>=' & Date(MonthStart(Today(), 1), 'D/MM/YYYY') & '<=' & Date(MonthStart(Today(), 1) + 59, 'D/MM/YYYY'))"}>} [Units])

(Anil Babu) vMonthStartDate is MonthStart(floor([Date]))

SO5 has the right total 461648 just need it in the 239995 cell.

Thanks,
Chris

Re: Set analysis range

sunny_talwar — Thu, 25 Aug 2016 13:12:59 GMT

Try this out:

=RangeSum(Below(TOTAL Sum([Units]),0,2))

Re: Set analysis range

chris_munns — Thu, 25 Aug 2016 13:25:00 GMT

Thanks again;

SO6 (Sonny T) =RangeSum(Below(TOTAL Sum([Units]),0,2))

Re: Set analysis range

sunny_talwar — Thu, 25 Aug 2016 13:52:10 GMT

Would you be able to share your qvw file? I see what the issue is, but will have to play around with it to get you the output you are looking for.

Re: Set analysis range

chris_munns — Thu, 25 Aug 2016 21:14:14 GMT

Hi Sunny,

Its tricky to share without some prep work on the file.

I am wondering if I create a new table with the Units summarised for [MonthStart] to ([MonthStart] +59).

This should give me 1 record per MonthYear.

Data granularity is;

SKU
Order#
Date
Warehouse
Units

Ideally, then 1 record per SKU and Wahrehouse per MonthStart.

Thanks,

Chris

Re: Set analysis range

othniel2014 — Thu, 25 Aug 2016 21:41:08 GMT

Try this in the script you must add a field ---> Num(Date) as DateNum

Then use this in the graph.

Sum({1<

DateNum={$(='">= ' & $(=num($(=MonthStart(floor(Num(Date)))))) & ' <= ' & $(=num($(=MonthStart(floor(Num(Date))) + 59 ))) &'"')}

>} [Units])

Re: Set analysis range

chris_munns — Fri, 26 Aug 2016 06:11:10 GMT

Hi all,

Hopefully solved a different way by loading distinct MonthStart and EndDate (MonthStart+59).

Then outer join to the Sales orders transaction table creating ~2m records and stored this in a temp qvd then dropped. Next reloaded the temp qvd summarising Units there [Data] between [MonthStart] and [EndDate] then grouping by [MonthStart] , [EndDate]. Approx 62k records.

This second temp table I then concatenate to the link table with all the other data with the datekey on MonthStart.

SO1 =sum(UnitsNew) - also these is criteria to ignore the current month, hence 0 in Aug-16.

SO is SO1 / 59 * 7 to calc a weekly 'disposal rate'

Next task is to align to 'Projected Stock' and calc how many weeks stock is on hand for each MonthYear.

Thanks,

Chris

Re: Set analysis range

chris_munns — Fri, 26 Aug 2016 07:50:23 GMT

Hi Othniel,

I did try this though i am not sure the syntax is quite right. See below;

I didn't get a chance to check further.

Thanks,
Chris

Re: Set analysis range

sunny_talwar — Fri, 26 Aug 2016 08:25:52 GMT

I think why don't you look into The As-Of Table‌. I think this is the best solution for your issue.