How to change values based on column data

Comments

3 comments

  • Avatar
    Nathan Giusti

    Hey, 

    An easy solution I see would be to create a new column in your table that is the qty*price for each product and year. 

    Then you can just sum this new column in your table. 

    Does that make sense?

    Cheers,

    Nathan

    1
    Comment actions Permalink
  • Avatar
    Anjali Yadav

    Hi

    +1 to the Nathan 's approach it is  simple and mostly works , but if you have any  filters on the dashboard that might affect the product price that the quantity should be multiplied with  then it  might not be feasible to pre-calculate it in the cube as he as advised, In that case  you will have to follow this link to calculate weighted average -https://support.sisense.com/hc/en-us/articles/360009144534-Calculating-Weighted-Average .

    Another way would be to add some custom javascript in the Edit Script of the widget to hide the Year column of the pivot on the fly after the numbers are calculated by Sisense. 

    That said, I haven't tried any of these two myself.  Good luck!

    An

    1
    Comment actions Permalink
  • Avatar
    Ido Darnell

    Hi @Jyothi,

    I understand from your screenshots that you want to not show the year column correct?

    If this is case you will need to use a multi-pass aggregation

    This functionality basically enables you to perform a nested query within a formula, so by introducing the Year grouping into your calculation, and then removing the year column, you will get the required value, your formula should look something like this:

    Purchase Cose = SUM([Years in OrderDate],[Total StockedQTY]*[Total UnitPrice])

    You're basically grouping the product between the stockedQTY and UntiPrice, then summing those per year,

    To test this, add a sub total to your years column in the pivot table, that value should match the value when removing the year grouping.

    Please don't hesitate to reach out, we are always here to help,

    Thanks,

    Ido

    Ido.darnell@qbeeq.pl

    QBeeQ.pl

    Sisense Partner of the Year 2019!

    0
    Comment actions Permalink

Please sign in to leave a comment.