topic Re: Aggregate 3 months data on set analysis in QlikView

Aggregate 3 months data on set analysis

Thu, 25 Aug 2016 09:43:19 GMT

Good day,

I have a quarterly report that aggregates months data based on the quarter selected.

I have this expression on my object and it just doesn't return any thing,

is there some incorrect in my expression?:

Sum({<Year={"$(=Year(MAX(Date)))"},
Date={">=$(=MonthStart(AddMonths(Max(Date),-2)))<=$(=MonthEnd(AddMonths(Max(Date),+0)))"}
>}WRITTEN_OFF_AMT)

Your assistance will be highly appreciated,

Mbini

Re: Aggregate 3 months data on set analysis

sunny_talwar — Thu, 25 Aug 2016 09:46:52 GMT

Try this:

Sum({<Year, Date={"$(='>=' & Date(MonthStart(Max(Date), -2), 'DateFieldFormatHere') & '<=' & Date(MonthEnd(Max(Date), 0), 'DateFieldFormatHere))"}>}WRITTEN_OFF_AMT)

Re: Aggregate 3 months data on set analysis

Thu, 25 Aug 2016 10:15:49 GMT

I tried this:

Sum({<//Year,
Date={"$(='>='& Date(MonthStart(Max(Date),-2),'DD/MM/YYYY') & '<=' & Date(MonthEnd(Max(Date), 0),'DD/MM/YYYY'))"}>}WRITTEN_OFF_AMT)

And I still dont get anything!!

Re: Aggregate 3 months data on set analysis

sunny_talwar — Thu, 25 Aug 2016 10:16:46 GMT

What are you getting and what do you expect to see?

Re: Aggregate 3 months data on set analysis

Thu, 25 Aug 2016 10:34:50 GMT

for this:

Date(MonthStart(Max(Date),-2)

I get: 01/04/2015

Date(MonthEnd(Max(Date), 0)

I get 30/06/2015

but when I have it on the expression as a whole:

Sum({<Year={"$(=Year(MAX(Date)))"},
Date={">=$(=MonthStart(AddMonths(Max(Date),-2)))<=$(=MonthEnd(AddMonths(Max(Date),+0)))"}
>}WRITTEN_OFF_AMT)

I get no results

Re: Aggregate 3 months data on set analysis

sunny_talwar — Thu, 25 Aug 2016 10:41:19 GMT

Would it be possible to share a sample?

Re: Aggregate 3 months data on set analysis

Thu, 25 Aug 2016 12:12:41 GMT

its quite a huge app i wont be able to share it over the net

Re: Aggregate 3 months data on set analysis

sunny_talwar — Thu, 25 Aug 2016 12:16:55 GMT

See if this helps:

Preparing examples for Upload - Reduction and Data Scrambling

Re: Aggregate 3 months data on set analysis

jmvilaplanap — Thu, 25 Aug 2016 12:30:05 GMT

I normally try to convert all the dates to numbers (and if is possible round numbers).

For example, I use Floor(Date) AS Date and another table if I want to show formated dates in the dashboard.

With this solution I can opperate with dates without format problems.

Try to use the dates without format.