Rangesum Query

[dmac1971]

Hi the following expression works perfectly in Qlikview :

RangeSum(Above(Count({<Measure = {'InitialDate'},Total_Working_Hours = {">20"}, WeekDay=>}Distinct SERIAL#), 0, RowNo()))

However will not work in Sense, result ends up being over 500k, however should only be over 3k.  Load scripts are identical in both pieces of work, however seems Sense is treating these differently.

I have an additional piece of script around calendar to allow cumulative count etc, I'm guessing Sense treats this differently than QV does?.

AsOfMonth:
LOAD
Month as AsOfMonth
,date(addmonths(Month,-iterno()+1)) as Month
RESIDENT Calendar
WHILE iterno() <= month(Month)
;

[stevedark · Accepted solution]

Hi @dmac1971 

A couple of things to check;

How many rows are in your AsOfMonth table? Is this what you expect?
Should you add a DISTINCT to the AsOfMonth load to remove duplicates?
Is the AsOfMonth associated in the data model, or is there a data-island / cartesian join
Does the Sense table have the same dimension as the QlikView one

Sorry I don't know a hard and fast answer, but hopefully something there will turn up a Result.

Steve

[sunny_talwar]

What is the dimension when you are using this? Is it Month of AsOfMonth? I would try using AsOfMonth and this expression

Count({<Measure = {'InitialDate'}, Total_Working_Hours = {">20"}, WeekDay>} DISTINCT [SERIAL#])

[dmac1971]

Senses autocalendar along with my own calendar had broken everything, now sorted. 

I built a straight table to check with basic calcs and seen how everything was getting inflated so that lead me to actually think what might be the problem!  Doh!

Thanks for replies.

[stevedark]

And that is one of the reasons that I don't use Sense's autocalendar feature.

I often create a variable which takes a parameter with a date that then creates all of the different break downs.

Thanks for confirming you now have it working.