Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

AddMonths syntax with set analysis

Hi Fellow Qlikers,

I have the following code which works as expected:

COUNT ({<DateReconciled = {'>=$(vStartDate)'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)

However I want to obtain 6 months  prior i.e Addmonths (x, -6)  as follows but no joy...

COUNT ({<EstimatedDate = {'>=AddMonths($(vStartDate),1)'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)

Can anyone see an obvious error here as I cannot?

Thanks  & Kind Regards

Mike

4 Replies

Re: AddMonths syntax with set analysis

Try this

Count({<EstimatedDate = {'>=$(=AddMonths($(vStartDate),1))'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)

Not applicable

Re: AddMonths syntax with set analysis

Hi Sunny,

Thanks for your reply. This  modification shows ALL the data within the date range i.e. no  filter is applied which is better than my nothing but not quite what I want given the data  goes back to June 2000 and I am showing month by month payments. Any further ideas?

Thanks

Mike

Re: AddMonths syntax with set analysis

Within the date range? What is your date range? May be try this

Count({<EstimatedDate = {'>=$(=AddMonths($(vStartDate),-6))<=$(=$(vStartDate))'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)

Does this give you what you want? If not, then please share more information about the format or EstimatedDate and what is vStartDate? Can you share its definition? Also, look here

Dates in Set Analysis

Not applicable

Re: AddMonths syntax with set analysis

Hi Sunny,

I have found it I need to remove the extra $ as follows:

Count({<EstimatedDate = {'>=$(=AddMonths( $ (vStartDate),1))'},TransactionTypeID = {1}, IsReversal = {0}, SaleYear=, SaleMonth=, SaleYearMonth=, SaleDate=>} NetValue)


i.e. the one before (vStartdDate)  with a dash crossed out in the example above.

Many thanks for your help

Mike

Community Browser