topic Re: Showing data quarterly from month data in QlikView

Showing data quarterly from month data

deepakqlikview_123 — Tue, 08 Sep 2015 08:04:15 GMT

Hi All,

PFA.

Below is the requirement.

When data for Jan,Feb ,Mar is present then make Jan+Feb+Mar for indicator value and show data for Q1 on march.

Similirly for Q2 on jun for Apr+May+jun

Q3 on sept for jul+aug+sept

And Q4 on dec for oct+Nov+Dec

If any month in any of quarter is not present then data for that quarter should not display

eg suppose for 2001 oct is absent so Q4 that is dec for 2001 shoud not display.

I am using below fomula

=if(mod(Month(DATE),3) = 0, If(month(DATE) -1 = Month(Above(DATE)) and (month(DATE) -2) = Month(Above(Above(DATE))),

Above(Text)+Above(Above(Text))+ Sum(INDICATOR_VALUE)), Sum({<INDICATOR_NAME={'FRCOSTRT Index = France Housing Starts Total'},

MONTH={3,6,9,12}>}INDICATOR_VALUE)

)

and DATE is domension.

Req is to show this in bar chart

Re: Showing data quarterly from month data

Tue, 08 Sep 2015 09:47:32 GMT

PFA

Re: Showing data quarterly from month data

Tue, 08 Sep 2015 10:05:48 GMT

Hi,

in the script you can add this,

load *,

......

'Q' & ceil(num(month(Date))/3) as Quater_Month,

......

residant ......;

Re: Showing data quarterly from month data

deepakqlikview_123 — Wed, 09 Sep 2015 04:40:39 GMT

Hi All,

Still it is not giving desired result.Can you please suggest on this.

Thanks

Re: Showing data quarterly from month data

sujeetsingh — Wed, 09 Sep 2015 04:58:31 GMT

Your line as below :

If any month in any of quarter is not present then data for that quarter should not display

eg in my data data for oct 2001 is absent so Q4 that is dec for 2001 shoud not display

To achieve this you can use subpress nulls

Re: Showing data quarterly from month data

qlikviewwizard — Wed, 09 Sep 2015 05:46:12 GMT

Hi,

Check the attached file. Hope this will help.

Thank you.

Re: Showing data quarterly from month data

deepakqlikview_123 — Wed, 09 Sep 2015 06:06:16 GMT

Hi All,

PFA.

Below is the requirement.

When data for Jan,Feb ,Mar is present then make Jan+Feb+Mar for indicator value and show data for Q1 on march.

Similirly for Q2 on jun for Apr+May+jun

Q3 on sept for jul+aug+sept

And Q4 on dec for oct+Nov+Dec

If any month in any of quarter is not present then data for that quarter should not display

eg in my data data for oct 2001 is absent so Q4 that is dec for 2001 shoud not display.

I need to show this in bar chart, but i could not find hhe solution please suggest

Re: Showing data quarterly from month data

qlikviewwizard — Wed, 09 Sep 2015 06:12:41 GMT

If any month in any of quarter is not present then data for that quarter won't be display.

What is Jan+Feb+Mar?

Re: Showing data quarterly from month data

deepakqlikview_123 — Wed, 09 Sep 2015 06:42:16 GMT

Jan+Feb+Mar is for Q1 need to be show Jan+Feb+Mar entire sum on Mar. if any month in respective quarter is not present then bar for that quarter should not display

Re: Showing data quarterly from month data

deepakqlikview_123 — Wed, 09 Sep 2015 09:44:39 GMT

All,

Can you please suggest on this.

Thanks

Re: Showing data quarterly from month data

sunny_talwar — Thu, 10 Sep 2015 11:23:27 GMT

Not sure why you have the last expression on failure of if(mod(Month(DATE),3) = 0, -> Sum({<INDICATOR_NAME={'FRCOSTRT Index = France Housing Starts Total'}, MONTH={3,6,9,12}>}INDICATOR_VALUE)

But this seems to add up the last three months of data:

=If(Mod(Month(DATE),3) = 0,

If(Month(DATE) -1 = Month(Above(DATE)) and

(Month(DATE) -2) = Month(Above(Above(DATE))),

Above(Sum(INDICATOR_VALUE)) + Above(Above(Sum(INDICATOR_VALUE))) + Sum(INDICATOR_VALUE)))

With regards to October 2001 missing, I actually see the data. Is it after you make a selection in country the data is not present?

Let me know if I am moving in a completely wrong direction here.

Best,

Sunny

Re: Showing data quarterly from month data

sunny_talwar — Thu, 10 Sep 2015 12:34:10 GMT

This?

Expression:

=If(Mod(Month(DATE),3) = 0,

If(Month(DATE) -1 = Month(Above(DATE)) and

(Month(DATE) -2) = Month(Above(Above(DATE))),

Above(Sum({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE)) +

Above(Above(Sum({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE))) +

Sum({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE)))

Re: Showing data quarterly from month data

qlikviewwizard — Fri, 11 Sep 2015 04:17:14 GMT

Hi Deepak,

Please close the thread by selecting Helpful/Correct Answer. Thank you.

Re: Showing data quarterly from month data

sunny_talwar — Fri, 11 Sep 2015 06:52:26 GMT

Does this resolve the issue?

=If(Mod(Month(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE)),3) = 0,

If(Month(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE)) -1 = Month(Above(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE))) and

(Month(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE)) -2) = Month(Above(Above(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE)))),

Above(Sum({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE)) +

Above(Above(Sum({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE))) +

Sum({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE)))

Output:

Re: Showing data quarterly from month data

sunny_talwar — Fri, 11 Sep 2015 06:56:51 GMT

Deepak I see two values on a single date, how are you planning to handle these? For example: 3/31/2015 and 6/30/2015 are repeating with different values.

Re: Showing data quarterly from month data

deepakqlikview_123 — Fri, 11 Sep 2015 07:05:48 GMT

that is data issue

Re: Showing data quarterly from month data

sunny_talwar — Fri, 11 Sep 2015 09:29:26 GMT

Yet another attempt

Expression:

=If(Mod(Month(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE)),3) = 0,

If(Month(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE)) -1 = Month(Above(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE))) and

(Month(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE)) -2) = Month(Above(Above(Only({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}DATE)))),

Above(Max({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}Aggr(Max({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE), DATE))) +

Above(Above(Max({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}Aggr(Max({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE), DATE)))) +

Max({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}Aggr(Max({<INDICATOR_NAME = {'FRCOSTRT Index = France Housing Starts Total'}>}INDICATOR_VALUE), DATE))))

Output:

Re: Showing data quarterly from month data

deepakqlikview_123 — Mon, 28 Sep 2015 11:05:32 GMT

Hi Sunny,

On mouse hover selection it showing no data to display.

IT has to show atleast selected bars.

Please suggest

Re: Showing data quarterly from month data

deepakqlikview_123 — Thu, 01 Oct 2015 06:01:20 GMT

jagan‌ can you please suggest on this.