topic Re: Re: Re: Need expression for end of month calculation in QlikView

Need expression for end of month calculation

Tue, 16 Sep 2014 16:28:55 GMT

Hi All,

Can any body help expression for below:

Table:

ID, Date.

If I choose Month July, I have to get total no of records where Date <= 31 July’ 2014.

If I choose Year 2013 and (no month chosen), I have get total no of records where Date <= 31 Dec 2013.

If I choose Quarter (Q1), I have to get total no of records where Date <= 31 March.

If I choose Q2, I have to get total no of records where Date <= 31 June.

Thanks,

Lakshmi.

Re: Need expression for end of month calculation

Tue, 16 Sep 2014 17:08:11 GMT

Hi All,

Any one help?

Re: Need expression for end of month calculation

JonnyPoole — Tue, 16 Sep 2014 17:44:06 GMT

If you have a Date field it could be best to use that in your SET ANALYSIS:

In your chart , use an expression like this where 'Date' is a date field in your data model and vMaxDate is a variable with the formula: =max(Date).

sum( {$<Year=,Quarter=,Month=, Date= {"<=$(=date(vMaxDate))"}>} Sales)

This will find out the maximum date in user's selection and give every data point that is <= that same date. Year= and Quarter= and Month= ensures all the preceding dates are included.

Re: Re: Need expression for end of month calculation

Tue, 16 Sep 2014 18:33:48 GMT

Hi Jonathan,

Sorry, the above is not working for me i want like this.

If I selected 2013 July month i want to count max date of July 2013 to oldest record.

EX : in 2013 for July month 23 date is highest so i want to count from 23/July/2013 to 31/12/2012 till this count. (below example i have data till 2012 if i have 2011, 2010 .... so on i want to count all.

one more thing is if i chose only month so it should take 2014 and selected month and max date to do count.

If I choose Month July, I have to get total no of records where Date <= 31 July’ 2014.

If I choose Year 2013 and (no month chosen), I have get total no of records where Date <= 31 Dec 2013.

If I choose Quarter (Q1), I have to get total no of records where Date <= 31 March.

If I choose Q2, I have to get total no of records where Date <= 31 June.

Thank you very much for your help,

Thank you

Lakshmi

Re: Re: Need expression for end of month calculation

Tue, 16 Sep 2014 19:17:23 GMT

Hi All,

Any help please?????

Re: Re: Re: Need expression for end of month calculation

JonnyPoole — Tue, 16 Sep 2014 19:48:59 GMT

Here is an example.

I cleaned up your data load with this syntax: (where Temp is the name of the INLINE table load)

NoConcatenate

Data:

load

Count,date(date#(Date,'DD/MM/YYYY')) as Date,day(date#(Date,'DD/MM/YYYY')) as Day, month(date#(Date,'DD/MM/YYYY')) as Month, Year(date#(Date,'DD/MM/YYYY')) as Year

Resident Temp

where date(date#(Date,'DD/MM/YYYY')) <> '-';

drop table Temp;

Then i created a variable vMaxDate with this formula:

=date(max(Date),'MM/DD/YYYY')

And the chart expression to bring back only dates earlier than the max date in the user's selection is:

= sum( {$<Year=,Day=,Month=, Date= {"<=$(=date(vMaxDate))"}>} Count)