topic Re: chart help. in QlikView

chart help.

userid128223 — Thu, 31 Jan 2013 18:10:55 GMT

Hi Everyone

I have a chart with usual selection.

Company name

Sales person

Month

Year

Quarter

I have a calculated field called TimesRemaining.

=Sum({$<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}TimePurchasedHours) - Sum({$<BILLABLE = {'T'}, ServiceDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}HoursWorked)

I have 2 charts

Full history regardless of selection: this charts works fine. (here I disable all the selection in set analysis, hence my TimeRemaining is calculated for all period for that company)

However when I try to do history by month (now I allow user to select time like day, month, quarter)

The TimeRemaing part show only that month instead of showing for entire period.

I am confused on how to get this to work.

Re: chart help.

Gysbert_Wassenaar — Thu, 31 Jan 2013 18:36:46 GMT

If you want to ignore your selections in your expression you can change it to:

=Sum({1<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}TimePurchasedHours) - Sum({1<BILLABLE = {'T'}, ServiceDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}HoursWorked)

Maybe you want to change today() to max(TimePurchaseDate) and max(ServiceDate) so you the 18 months period ends with the maximum TimePurchaseDate and ServiceDate of the day, month or quarter of your selections.

If you don't want to ignore all selections, you'll have to add the fields that influence the selections in TimePurchaseDate and ServiceDate to the expression

=Sum({$<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}, Year=,Quarter=,Month=>}TimePurchasedHours) - Sum({$<BILLABLE = {'T'}, ServiceDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}, Year=,Quarter=,Month=>}HoursWorked)

Change Year, Quarter and Month to the respective names of your fields.

Re: chart help.

userid128223 — Thu, 31 Jan 2013 18:48:10 GMT

Hi Gysbert

attach are 2 images of snapshot of 2 charts

In this chart I have allowed user to select months and year, hence the chart is adjusted as per selection. But you see the TimeRemaining as -10 that is incorrect, because it is only showing me for that month user has selected, I want the time remaining to be total of all the word for that company.

This below chart. I have disallowed user to do any selected under month or year. hence TimeRemaining comes out right.

Re: chart help.

userid128223 — Thu, 31 Jan 2013 18:51:26 GMT

how can i get this formula to work so that it will have no impact of time selection. however will do total per company.

in chart, it gets adjusted when i select time.

=Sum({$<BILLABLE = {'T'}, TimePurchaseDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}TimePurchasedHours) - Sum({$<BILLABLE = {'T'}, ServiceDate = {'>=$(=ADDMONTHS(TODAY(),-18))'}>}HoursWorked)

Re: chart help.

userid128223 — Thu, 31 Jan 2013 19:11:36 GMT

taking 1 instead of $ wont help because i dont want entire data set, i want entire data set per company.

Re: chart help.

Anonymous — Thu, 31 Jan 2013 19:26:07 GMT

Hi,

Can You please provide sample applictaion or data.

Regards

Re: chart help.

userid128223 — Thu, 31 Jan 2013 19:42:05 GMT

it will be hard for me to do that. sensitive data.

Re: chart help.

Anonymous — Thu, 31 Jan 2013 20:58:11 GMT

Hi,

i am asking just sample data,not your data

Re: chart help.

userid128223 — Thu, 31 Jan 2013 21:35:56 GMT

i understand but how to do i replicate the data set with a fake one.