topic Re: Alternate two dimensions in one dimension in QlikView

Alternate two dimensions in one dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

Hoping you guys could help me with something I've been stuck on for awhile.

My company wants to view Sales in a pivot chart in a pivot chart where it shows the brand and then the sales for each month of the quarter then the sales for the quarter, then the sales for each month then a column with the sales for the quarter etc.

Brand Jan-2017 Feb-2017 Mar-2017 Q1-2017 Apr-2017

ABC Sales a Sales b Sales c Sales(a+b+c) Sales d

ZYX Sales a Sales b Sales c Sales(a+b+c) Sales d

I have fields for MonthYear, but I don't know how to get the quarter in there especially in the order I need it.

Ideally I could just use a formula in the pivot object itself and not have to add dimension values into the script as it would require altering many other objects.

Any thoughts?

Much appreciated,

Lee

Re: Alternate two dimensions in one dimension

MarcoWedel — Wed, 12 Jul 2017 23:28:34 GMT

Hi,

I guess a straight forward approach here would be to just use both Month and Quarter dimensions together with a partial sum in a pivot table like this:

Another solution might be to create a combined MonthQuarter field e.g. like this:

tabSales:
LOAD *,
    Dual('Q'&Ceil(Month(MonthYear)/3)&'-'&Year(MonthYear),QuarterStart(MonthYear)) as Quarter;
LOAD MonthName(AddMonths(MakeDate(2017),RecNo()-1)) as MonthYear,
    Ceil(Rand()*100) as Sales,
    Chr(64+IterNo())&Chr(65+IterNo())&Chr(66+IterNo()) as Brand
AutoGenerate 12
While IterNo()<=10;

tabMonthQuarter:
LOAD Distinct
    MonthYear,
    MonthYear as MonthQuarter
Resident tabSales;

LOAD Distinct
    MonthYear,
    Dual(Quarter,QuarterEnd(Quarter)) as MonthQuarter
Resident tabSales;

hope this helps

regards

Marco

Re: Alternate two dimensions in one dimension

Anonymous — Mon, 17 Jul 2017 13:16:16 GMT

Both worked thank you very much!

Lee

Re: Alternate two dimensions in one dimension

MarcoWedel — Tue, 18 Jul 2017 22:51:46 GMT

you're welcome

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco