Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
data_RN
Contributor III
Contributor III

Rangesum with time criteria

Last week I posted a question regarding a range sum with varied requirements, of which I got the answer for (thank you!) How can I remove a dimension from a table when it is part of a calculated expression? and now they are wanting to limit the programs included by adding a time limit.

I need to include the Program IF there will also be a "Go-To-Stage Meeting Date" in the next two months.

I have been trying to figure it out, but I can only manually calculate if there is a meeting date in the next months by each stage (my example is in the attached file), but I can't figure out how to incorporate it into the existing RangeSum calculation. Below is sorta what I would like - I know, the syntax is totally wrong 😊

Is there an easy fix for this?

RangeSum(
 Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}, [Go-To-Seed Meeting Date] = between Today() and Today()+60}>}[Program]),
Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]), [Go-To-Alpha Meeting Date] = between Today() and Today()+60}>}[Program]),
Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program]) [Go-To-Beta Meeting Date] = between Today() and Today()+60}>}[Program]),
)

 

Thanks again in advance!

1 Solution

Accepted Solutions
sunny_talwar

I am still not 100% sure I understand, but try this

RangeSum(
	Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}, [Go-To Seed Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>} [Program]),
	Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}, [Go-To-Alpha Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>}[Program]),
	Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}, [Go-To-Beta Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>}[Program])
)

View solution in original post

6 Replies
sunny_talwar

Which of the two charts are we trying to fix here? What is the exact number you are hoping to see?

data_RN
Contributor III
Contributor III

Hi Sunny, 

I am looking to see the number of programs that have completed their list for that stage AND have a meeting date in the the next 2 months for that stage.  So something like this:

 

GroupCount (Program)Combo -  List done for current stageList done for current stage AND has a meeting planned in next 2 months
 952
a322
b310
c320

 

Is that clearer?

Thanks for your help!

sunny_talwar

Why can't you just use this same expression from the above chart in the below chart?

(Count(If([Go-To Seed Meeting Date] >Today(),0)))-(Count(If([Go-To Seed Meeting Date] >Today()+60,0)))

image.png 

data_RN
Contributor III
Contributor III

Hello again, 

It only gives me part of the answer.  I need the sum total using the current stage.  I have only figured out how to do it by one stage at a time.

Here is what I mean:

GroupStageCount (Program)List Done for Current StageMeeting Dates within 60 days for Seed Stage OnlyMeeting Dates within 60 days for Alpha Stage OnlyMeeting Dates within 60 days for Beta Stage OnlyCurrent Stage, List Done IN Time Period  for Current stage - THIS IS WHAT I NEED
  952212
aSeed111001
aAlpha110101
aSeed1 100-
bBeta1 000-
bSeed11010-
bAlpha1 000-
cBeta1 000-
cBeta11000-
cAlpha11001-

 

I updated the file with the above and attached.  The last column is the one I can't figure out.  I had hoped I could alter your original RangeSum formula to include the time period check for the Go-To- "X" Meeting Date, but I can't seem to get it to work 😕

sunny_talwar

I am still not 100% sure I understand, but try this

RangeSum(
	Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}, [Go-To Seed Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>} [Program]),
	Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}, [Go-To-Alpha Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>}[Program]),
	Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}, [Go-To-Beta Meeting Date] = {">=$(=Date(Today()))<=$(=Date(Today() + 60))"}>}[Program])
)

View solution in original post

data_RN
Contributor III
Contributor III

AWESOME!  That is exactly it!

Thank you 🙂