topic Re: Cumulative data in chart. in QlikView

Cumulative data in chart.

agni_gold — Thu, 12 May 2016 08:39:23 GMT

Hi All ,

I have a booking fact table in which some fields like , Segment and Date, and ..... so on columns present , now i have created one calendar and linked with fact table date.

Now i have created chart which is showing comparison between current year booking vs previous year booking.

but in case if we are selecting current month ,booking will happen till current date , and for previous year all days data present in table ,

i am using below expression for current booking expression and previous year booking expression,

Current year booking expression is

RangeSum(above(Sum({<[Segment Entity]-= {""},Date= {">=$(=Min([Month Start Date]))<=$(v_Date)"}> }[Sale Amount USD])/1000000,0,
Sum({<Date= {">=$(=Min([Month Start Date]))<=$(v_Date)"}> }Day)) )

where v_Date is current Date.

Previous year booking expression

RangeSum(above(Sum({<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }[Sale Amount USD])/1000000,0,
sum( {<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }Day)) )

It is working file when i am not applying any filter in dashboard except year and month, suppose i have applied filter in Segment field , previous year booking bars are also coming till date . like below

But i want till full month.

Please suggest where i am doing wrong.

Re: Cumulative data in chart.

sunny_talwar — Thu, 12 May 2016 08:43:05 GMT

May be this:

If(Sum({<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }[Sale Amount USD]) > 0, RangeSum(above(Sum({<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }[Sale Amount USD])/1000000,0,
sum( {<Year=,Date= {'>=$(v_prvmonthstart)<=$(v_prvmonthend)'}> }Day)) ))

Re: Cumulative data in chart.

jonathandienst — Thu, 12 May 2016 08:52:52 GMT

The problem could be one of several causes:

There is no data for the other days
Some selection is suppressing the other days
The variables used in the expression (for which you provided no information)

Having said that, I would also consider modifying the expressions to something like

RangeSum(Above(Sum({<[Segment Entity]-= {""},Date= {">=$(=Min([Month Start Date]))<=$(v_Date)"}> } [Sale Amount USD])/1000000, 0, RowNo()))

RangeSum(Above(Sum({<[Segment Entity]-= {""},Date= {">=$(=Min([Month Start Date]))<=$(v_Date)"}> } [Sale Amount USD])/1000000, 0, 31))

but I don't think that is your problem.

Re: Cumulative data in chart.

agni_gold — Thu, 12 May 2016 08:53:43 GMT

No Sunny , it is now working ,

actually when i am selecting segment then dates are also filtered and max possible date is gone till current date, i think because of this previous year date is also restricting ,

Please suggest.

Re: Cumulative data in chart.

sunny_talwar — Thu, 12 May 2016 08:57:06 GMT

then restrict the selection of segment on previous year dates.I guess I don't have the sample in front of me so can't really see and play around with it.

Re: Cumulative data in chart.

agni_gold — Thu, 12 May 2016 09:13:14 GMT

Sunny , if i restrict selection of segment then my requirement will not full fill,

Actually when i select any segment then based on that segment it should show comparison between current and previous year booking data.

Thanks

Re: Cumulative data in chart.

trdandamudi — Thu, 12 May 2016 09:17:41 GMT

Is it possible to post a small sample so that we can help you out...

Re: Cumulative data in chart.

sunny_talwar — Thu, 12 May 2016 09:20:49 GMT

Not restrict everywhere but where you are calculating the Max Date. May be like Max({<Segment>} Date)....

Re: Cumulative data in chart.

agni_gold — Thu, 12 May 2016 10:19:02 GMT

Hi Sunny , i have tried to make restriction , like below for v_prvmonthstart

=Date(min({<Segment=>}AddMonths([Month Start Date],-12)),'DDMMMYYYY')

and for v_prvmonthend

=Date(Max({<Segment=>}AddMonths([Month End Date],-12)),'DDMMMYYYY')

But still not working

Re: Cumulative data in chart.

sunny_talwar — Thu, 12 May 2016 10:24:57 GMT

Can you post images and show what is going on with and without selections (and what selections)