Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've a requirement to count the values from previous month last day.
Ex:
If I select Year 2015>Month Sep> I should count values from Aug-31st
If I select Year 2015>Quarter 2> I should count count values from Previous Quarter (Q1), Last Month of Q1(March) and last day in March
If I select Year 2015> I should get counts for Year 2014, Dec 31st
And all these scenarios are incorporated in single expression, it would be a single expression.
Individually my expression works
I can get previous month counts but can't get previous month last date.
Previous Month:
Count({< [Year Month Num] = {"$(=Date(Date#(Max([Year Month Num]),'YYYYMM')-1,'YYYYMM'))"}, Month = , [Month Year] = , Year =>}
DISTINCT Dimension)
Previous Year, Max Month:
Count({<Num_Month={$(=Max(Num_Month))}, LastMonthFlag={1} >}
DISTINCT Dimension))
But Last Day is not working, always gives me 0 if I give
Count({< Month={"=$(vPriorMonth)"},Day = {$(=Max(Day))} >}
DISTINCT Dimension)
But in text box it seem to work
vPriorMonth is Month(addmonths(max(Date),-1))
If somebody could help??
Thanks.
For quarter, ignore selection in Quarter field (in fact add all the date and time related fields where you might make a selection)
Count({<Date ={"$(=Date(MonthStart(Min(Date))-1, 'M/D/YYYY'))"}, Year, Month, Quarter>} DISTINCT ID)
Use MonthEnd ...
Would you be able to provide some same data to look at?
Trust me I tried everything available in community...
Monthend, variables etc
Both in script and frontend but doesn't seem to work
I'll try to make some dummy data and upload here....@ Sunny
Awesome
Please share some sample data with expected output.
I modified the QVW from this community thread to adjust as per my req.
QlikView App: Set Analysis - Prior Period Comparison
Please find the attachment.
In above thread the user explains how to get previous month also max of day but doesn't seem to work in an expression.
If you see in sheet1 I'm using
count({<Month={"=$(vPriorMonth)"},Date ={"$(=Monthend(Max(Date),-1))"} >}
distinct ID)
Which should give me ID counts for selected month, max day but it always gives me 0
I hope this would be sufficient...
Here I'd be trying to count the Distinct ID's in a given month only for last day in that month
Thanks.
If I select Year 2015>Month Sep> I should count values from Aug-31st
If I select Year 2015>Quarter 2> I should count count values from Previous Quarter (Q1), Last Month of Q1(March) and last day in March
If I select Year 2015> I should get counts for Year 2014, Dec 31st
You only mentioned from condition, what is the to condition here?
If you select Year 2015 and Month Sep, you will count from Aug-31st till what date? and similarly for other selections?