topic Re: Quarters for a line chart in a Calculated Dimension in QlikView

Quarters for a line chart in a Calculated Dimension

louwriet — Thu, 04 Sep 2014 09:34:07 GMT

Hi,

Would really appreciate urgent help with a calculated dimension that i am stuck with.

I have 2 CALCULATED dimensions for a line chart - one customers(top 4 - this is working) and then quarter grouping backwards up to 12 - QTR1, QTR2, QTR3..........QTR12. This calcualted dimension is currently returning null value for the expression sum(sales)

I started testing with one quarter back wards

= IF ([TIME] >= $(vMinMonth1) and [TIME] >= $(vMaxMonth1) ,'daterange1', null() ) currently this is always returning null

my vMinMonth1 variable is working = monthstart( AddMonths(Max(LatestDate), -2))

my vMaxMonth1 variable is working = monthend(AddMonths(Max(LatestDate)))

when i debug is see the values pulling through

= IF([TIME]>= 2014/03/01 and [TIME]<= 2014/05/14 , 'daterange1',null() ) but its showing null values for the sales that happen during this time period

Thank so much

Louw

Re: Quarters for a line chart in a Calculated Dimension

Gysbert_Wassenaar — Thu, 04 Sep 2014 09:41:17 GMT

2014/03/01 could be evaluated as 2014 divided by 03 divided by 01 = 671.333

Re: Re: Quarters for a line chart in a Calculated Dimension

louwriet — Thu, 04 Sep 2014 11:20:12 GMT

Hi Gysbert,

I am not with you what you are saying. Maybe i must explain a bit more what my problem is. The quarters that i want to create will be in a calulated dimension so that i can use that as x axis in chart.

my logic is : If date >= minmonth1 and date <= maxmonth1 then first point on axis QTR1

If date >= minmonth2 and date <= maxmonth2 then second point on axis QTR2 ............... UNTIL QTR12

the variables for minmonth and maxmonth is pulling in correct dates.

I don't know if the mistake is in my formula that i use or maybe the date format ? See attached excel where is used expressions to calsulate the sales for the qtrs but now in the line chart i need the quater daterange in x axis.

Thank you for your help.

Louw

Re: Re: Quarters for a line chart in a Calculated Dimension

Gysbert_Wassenaar — Thu, 04 Sep 2014 11:32:15 GMT

Can you post an example document that demonstrates the problem?

Re: Re: Re: Quarters for a line chart in a Calculated Dimension

louwriet — Fri, 05 Sep 2014 09:02:26 GMT

Hi Gysbert.

Attached is qvw where i explain what i am trying to achieve

Thank you for your help

Louw

Re: Re: Re: Re: Quarters for a line chart in a Calculated Dimension

Gysbert_Wassenaar — Fri, 05 Sep 2014 11:44:29 GMT

See attached qvw.

Re: Re: Re: Re: Quarters for a line chart in a Calculated Dimension

louwriet — Fri, 05 Sep 2014 14:19:58 GMT

Hi Gysbert,

When i used your suggested calculated dimension for the quarters on my real data is saw that it is taking real quarters backwards - so April 14, May 14 - Q2 2014 and Jan 14 , Feb 14, March 14 - Q1 2014 - this is correct for real quarters but what i need is rolling quarters backwards starting with March 14, April 14, May14 - Q1 Dec 13, Jan 14, Feb 14 - Q2 ------up to 12 rolling quarters backwards. Is this possible ?

On the ranking - customer came to me today now and ask that Customer 1 must always show and the top two of the rest. Meaning in the ranking show Customer 1 and rank the rest and display top 2.

Your help is really appreciated

Louw

Re: Re: Re: Re: Re: Quarters for a line chart in a Calculated Dimension

Gysbert_Wassenaar — Mon, 08 Sep 2014 08:10:15 GMT

For rolling quarters you may want to read this document: Calculating rolling n-period totals, averages or other aggregations

For the Customer1 + top 2 of the rest... something like:

sum({<PRODUCT={'Customer1'}>+<PRODUCT={"=rank(sum({<PRODUCT-={'Customer1'}>}Units))<3"}>}Units)

Re: Quarters for a line chart in a Calculated Dimension

louwriet — Tue, 09 Sep 2014 09:13:18 GMT

Hi Gysbert,

Thank you ! i got the rolling quarters working for my x axis - got the solution in the link that you refered to me - even got now the rolling 6 months working. Very helpfull link.

Your suggestion of expression works for me also, i just have one thing to overcome - all customers that falls outside of customer1 and the top 3 must be in a group 'others'. I need to show Customer1 the top 3 and then the rest in 'others'

Can one also work the 'others' in your suggested expression that is working now ?.

Thank so much

Louw

Re: Quarters for a line chart in a Calculated Dimension

Gysbert_Wassenaar — Tue, 09 Sep 2014 10:05:14 GMT

That needs a calculated dimension. Something like:

=If(PRODUCT='Customer1' or aggr(rank(sum({<PRODUCT-={'Customer1'}>}Units))<3,PRODUCT), PRODUCT, 'Others')

Re: Quarters for a line chart in a Calculated Dimension

louwriet — Tue, 09 Sep 2014 11:18:46 GMT

Hi Gysbert,

The calculated dimension worked, everyting is working now. I appreciate all the help with this

Regards

Louw