topic Re: Set analysis for three month in bar chart in QlikView

Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 06:23:33 GMT

Hi All,

In bar chart i need to show three month's data

If for a perticula data data is not present in previous month it should pic max date data of the month month

Thanks in advance.

Regards

Sivaram

Re: Set analysis for three month in bar chart

sushil353 — Tue, 07 Mar 2017 06:25:59 GMT

Hi,

Please share sample application

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 06:27:55 GMT

Hi Sushil Kumar,

Thank's for quick response

pfa..

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 06:35:32 GMT

In that example:

If i select 10/31/2016 i should get 8/31/2016 9/31/2016 is not there in the data i should get max date of 9th month data

any have i should show three month data in the bar chart.

Re: Set analysis for three month in bar chart

PrashantSangle — Tue, 07 Mar 2017 06:45:16 GMT

Hi,

Set anlaysis like

sum({<RECEIVE_DATE={">=$(=Date(AddMonths(max(RECEIVE_DATE),-2)))<=$(=Date(max(RECEIVED_DATE)))"}>}TTL_GL_ACCOUNT)

Regards,

Prashant Sangle

Re: Set analysis for three month in bar chart

Anil_Babu_Samineni — Tue, 07 Mar 2017 06:47:18 GMT

May be this?

if(RECEIVE_DATE='31/12/2016',Sum({<RECEIVE_DATE = {'>=$(=Month(RECEIVE_DATE,-2)) <= $(Max(Month(RECEIVE_DATE)))'}>}TTL_GL_ACCOUNT))

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 06:52:51 GMT

Hi Prashant,

I need to show only three bar in chat

ie..10/31/2016 & 8/31/2016 and 9/31/2016(9/31/2016) is not there in data it should pick max date ie 9/30/2016

this is what i need

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 06:55:40 GMT

Hi Anil,

You are hard coding 31/12/2016 but i need to pick based on selection of receive date list box

the bar chart should show three month's data based on pick of receiveing data it should behave dear.

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 07:12:17 GMT

Hi Prashant,

In my test.qvw if we select 26/10/2016 it is showing 3 months data

what my sinario is it should comare based on selection of receving date same day is not present in last month it should pick max day of month value ie..30/9/2016 if i select 31/10/2016

Re: Set analysis for three month in bar chart

sunny_talwar — Tue, 07 Mar 2017 07:34:13 GMT

May be this:

Sum({<RECEIVE_DATE={"$(=Date(Max(RECEIVE_DATE)))", "$(=Date(MonthEnd(Max(RECEIVE_DATE), -1)))", "$(=Date(MonthEnd(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT)

Re: Set analysis for three month in bar chart

sunny_talwar — Tue, 07 Mar 2017 07:38:28 GMT

Or just this:

Sum({<RECEIVE_DATE={"$(=Date(Max(RECEIVE_DATE)))", "$(=Date(AddMonths(Max(RECEIVE_DATE), -1)))", "$(=Date(AddMonths(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT)

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 08:49:39 GMT

Hi Sunny,

Thanks

If same day is present in last month i need to get it if not then only i need to get max day value

in my example 26/10/2016 is present in all the three months i should get it

if not present pick the max day of that month

Re: Set analysis for three month in bar chart

sumitjadhav — Tue, 07 Mar 2017 09:03:05 GMT

Hi Siva Krishna,

According to my Understanding ,on That Basis i have developed the code for your issue,please let me know ,wheteher it matches with your case.

Please try the below Script,

data:

LOAD *,

MonthName(RECEIVE_DATE) as Monthyear;

LOAD REGION,

COUNTRY,

TOTAL_GL_ACCOUNT,

RECEIVE_DATE

FROM

[path);

Join(data)

LOAD Monthyear,

date(max(RECEIVE_DATE),'dd-MM-yyyy') as Date1,

1 as Flag

Resident data

Group By Monthyear ;

Output:

Thanks & Regards,

Sumit Jadhav.

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 09:05:27 GMT

Hi Sumit,

Thanks

Can you send me that QVW file.

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 10:01:26 GMT

Hi Sunny

Please go through it for you reference

Could you please help me out

Regards

Sivaram

Re: Set analysis for three month in bar chart

sunny_talwar — Tue, 07 Mar 2017 12:03:51 GMT

Try this:

RangeSum(

Sum({<RECEIVE_DATE={"$(=Date(Max(RECEIVE_DATE)))"}>}TTL_GL_ACCOUNT),

If(Sum({<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT) = 0, Sum({<RECEIVE_DATE={"$(=Date(MonthEnd(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT)),

If(Sum({<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT) = 0, Sum({<RECEIVE_DATE={"$(=Date(MonthEnd(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT)))

Re: Set analysis for three month in bar chart

sivasiri99 — Tue, 07 Mar 2017 12:23:28 GMT

Hi Sunny,

If i selected 6th march 2017 it should be 6th Feb 2017 and 31th Jan 2017 because 6th Jan data is not available .

if selected day is present in previous month it should get else max day of previous month.

Regards

Sivaram.

Re: Set analysis for three month in bar chart

sunny_talwar — Tue, 07 Mar 2017 12:45:36 GMT

This should do it now

RangeSum(

Sum({<RECEIVE_DATE={"$(=Date(Max(RECEIVE_DATE)))"}>}TTL_GL_ACCOUNT),

If(Sum(TOTAL {<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT) = 0, Sum({<RECEIVE_DATE={"$(=Date(MonthEnd(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT), Sum({<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -1)))"}>}TTL_GL_ACCOUNT)),

If(Sum(TOTAL {<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT) = 0, Sum({<RECEIVE_DATE={"$(=Date(MonthEnd(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT), Sum({<RECEIVE_DATE={"$(=Date(AddMonths(Max(RECEIVE_DATE), -2)))"}>}TTL_GL_ACCOUNT)))