Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Sunny/Omar,
Thanks for your patience, I am a complete novice so this is hard for me.
I have a huge sales funnel of many different values ranged across lots of months in the format MMM-YY.
What I want to do is sum up all of the values for a defined date range, for example Jan-17 to Apr-17.
I think my example data set was very misleading as it only showed 1 value per month date.
In reality there would be hundreds of values in each month date.
It is the sum of the PWP (probability weighted pipeline) that I need to control the date for.
Hi Sunny
I'd want to see today's Month up to and including a defined cut off Month like Jun-17.
It is the kind of function the date slicer in Excel can do.
Thanks! 
We completely understand where you are coming from, but here are the few things to check and understand. Is your Date a true date field?
If you fixed your date (or is already in date format), next step is to understand how set analysis work
Once you have all this figured out, you will see how my and Omar's expression (one of the two based on the requirement) is exactly what you are looking for
Hi Sunny
If I am importing dates from excel that are not proper dates this could explain why I dont get good sorting behavior.
Is there a simple conversion in the import script?
Something to convert my excel MMM-YY into a Qlik recognised date?
Below you can see my attempt to filter these dates but I think Qlik is not treating them like dates.
Let's suppose we have this:
load *, Date(date#(Month1,'MMM-YY'),'MMM-YY') as Month;
load * Inline [
Month1, Value1, Value2, Value3
Jan-17, 2400, 300, 500
Feb-17, 500,400,600
Mar-17, 12000,8000,300
Apr-17 , 20,100,500
May-17, 4000,600,50
Jun-17, 1000,300,40
];
So we have 3 measures: sum(Value1), sum(Value2) and sum(Value3)
Now, if we choose a bar chart:
As dimension: Month
As measures:
1)sum(
As dimension: Month
As measures:
1)sum(Value1)
2)sum(Value2)
3)sum(Value3)
Value1)
2)sum(Value2)
3)sum(Value3)
We would have this:
Which is sum(Value1)+Sum(Value2)+Sum(Value3) by month:
Now we want to limit the months and have the sum only from month >=Jan-17 and <=Apr-17:
We need to alter our expressions:
As dimension: Month
As measures:
1)sum({<Month={"<=Apr-17 >=Jan-17"}>}Value1)
2)sum({<Month={"<=Apr-17 >=Jan-17"}>}Value2)
3)sum({<Month={"<=Apr-17 >=Jan-17"}>}Value3)
We will have :
Now: we want to cumulate these measures; we do not need the sum of vale1,2 and 3 by month; but we want them to be cumulative.
So we alter each expression:
1)Rangesum(above( sum({<Month={"<=Apr-17 >=Jan-17"}>}Value1),0,rowno()))
2)Rangesum(above( sum({<Month={"<=Apr-17 >=Jan-17"}>}Value2),0,rowno()))
3)Rangesum(above( sum({<Month={"<=Apr-17 >=Jan-17"}>}Value3),0,rowno()))
Result:
Please see the attached app (second sheet)
And refer to this thread to understand more the set analysis:
The links I shared showed you exactly what you need to do. besides Oman and Shiva have also shown what needs to be done