Alternate two dimensions in one dimension

[Anonymous]

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

[MarcoWedel]

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

[Anonymous]

Both worked thank you very much!

Lee

[MarcoWedel]

you're welcome

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco