Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
leefulford
Contributor
Contributor

Alternate two dimensions in one dimension

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

3 Replies
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:

QlikCommunity_Thread_267432_Pic2.JPG

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

QlikCommunity_Thread_267432_Pic1.JPG

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

leefulford
Contributor
Contributor
Author

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