topic Re: count field with previous month in QlikView

count field with previous month

Fri, 06 Jul 2012 15:48:30 GMT

I 'm new to this tool and i need kind help seniors..... could u pls help me out

I am using Line graph and in my expression i hav to include this logic

I hav a date field "X" and i want to count "Y" with the condition that count should include X+ previous 6 month and my dimenion is also "X"

i tried this logic

count(DISTINCT(if(Date(X)<monthstart(AddMonths(X,-6)) ,Y))

'm getting wrong value when i look at the source data i dont know where exactly 'm running out of logic so guide me out seniors...

many thanks in advance

Re: count field with previous month

Fri, 06 Jul 2012 15:57:19 GMT

Try:

Count(Distinct {<X={"<=$(=monthstart(AddMonths(X,-6)))"}>} Y). If X is a number then change it to (num(monthstart...).

Kiran Rokkam.

Re: count field with previous month

Sun, 08 Jul 2012 07:45:29 GMT

thanks for ur response but its showing 0 wt to do???

Re: count field with previous month

Sun, 08 Jul 2012 18:31:29 GMT

Expression tat 'm using currently is tat

count(DISTINCT(if(Date(DisputeLog.DateofLogging)<monthstart(AddMonths(DisputeLog.DateofLogging,6))

and date(DisputeLog.DisputePeriod)<MonthStart(AddMonths(DisputeLog.DisputePeriod,6))

and DisputeLog.DisputeType<>'Information' and

DisputeLog.Source<>'DecMes' and

DisputeLog.Status='Completed',DisputeLog.LogId)))

my two date fields are DateOfLogging and DisputePeriod and few conditions.... if i select some month for eg..... Mar-2012 i want to show the count Mar-2012 + Feb-2012.....+Oct-2011 and if i select Apr-11 it should giv me the count again Apr-11 + previous 6 months data like dynamic one and 'm using same date field as my dimension i used ur expression its showing 0 value.... could u pls help me out?

Re: count field with previous month

Mon, 09 Jul 2012 03:53:06 GMT

Try this:

For this to work ensure both your date fields are timestamps:

count(DISTINCT {<DisputeLog.Status={'Completed'},DisputeLog.Source-='DecMes'},DisputeLog.DisputeType-={'Information'},DisputeLog.DateofLogging={"<=$(=monthstart(AddMonths(Date(Max(DisputeLog.DateofLogging)),-6)))"},DisputeLog.DisputePeriod={"<=$(=monthstart(AddMonths(Date(Max(DisputeLog.DisputePeriod)),-6)))"}>} DisputeLog.LogId)

Regards,

Kiran Rokkam.