0

Pivot Table Column Order

I have a simple pivot table with three measures and one column (see screenshot). 

 

Is there a way to show each measure by the columns, and then the next measure by the columns? I'm on version 5.1.

Quantity                          ExtendedPrice

2019/Jul, 2019Aug         2019/Jul, 2019Aug

 

Thank you,

Szara

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • How much latitude do you have with schema design?    

     

    There may be an easy "checkbox" way to do this in Incorta, but I don't know it  ;-)     Also, I haven't tried the below yet so grain of salt and all that ...

     

    What I've seen in the past is the implementation of a measure dimension ( simple e.g.   NAME, VALUE  )    If it's not easily attained from the source, you can created it in the schema w/ a CASE statement explicitly naming the measures.    In your case you could either build everything into that one table/view or include the order number so you can get the status that way.

    Like 1
  • I think, you are asking what is the count for order status based on quantity, extended price, extended cost and margin wise?( whose staus is invoiced)

    then how can pivot table will dispaly two results at a time.

     

    I think it will not display the result what you are expecting. because, measure will always give the result in numeric way.( Attached a screenshot for your reference)

     

    It is better to use aggregate table/listed table insights which will give the result how you are expecting.

    Like 1
  • Sorry that my explanation was not very clear; let me try again.  Below is a depiction of the pivot table layout in Incorta.  The users want a different order of the columns.  I hope this is more clear.  At this point I cannot make changes to the schema, but that is possible in the long term.  

    Like
  • please look into my explanation.

    I have added the data into incorta like below.

       

    for above image(1) , if you added a measure like how you are expecting then, result will be in image(2)(Year/month is added under both col value & measure value)

     you can't show any table without a measure. it will show blank.(If you remove year/month from measure)

    So I have added a sample column(no.of orders) to the source data(image3) for your reference. then, result will be in image4.

     

        I think whatever image you attached, is from excel/.csv files not from incorta. because In incorta you can't see any listed/aggregated/pivot table without a measure. 

    So, Arrange your source data accordingly.

    Thank you.

    Like 1
  • I Think if Columns are available in your Dataset then you just need o swap the "Quantity" and "ExtendedPrice" Columns in the Columns Tray.

    Like
  • Hi Szara, I think there is no way to achieve this without some changes in schema level. 

    In case of you making some changes in schema in future, here is a simple Pyspark code which might help:

    df=df.selectExpr('date','order_status',"stack(4, 'Quantity', Quantity, 'Cost', Cost, 'Price', Price, 'Margin', Margin) as (Name, Value)")

    It will unpivot the original table to this:

      

    Based on this table you can generate the output:

     

    Like 2
Like Follow
  • 7 days agoLast active
  • 6Replies
  • 42Views
  • 5 Following

Product Announcement

A new community experience is coming! If you would like to have beta access to provide feedback, please contact us at community@incorta.com.