Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kshitijas
New Contributor III

Help in finding out formula!

Hello everyone,

          Finding difficulty in calculating formula:

I Have four tables and their fields as metioned below:

1) MISGrpMaster:

  • MISGrpID,
  • MISGrpName,
  • DispOrder

2) MISSubGrpMaster:

  • MISSubGrpID,
  • MISGrpID,
  • MISSubGrpName,
  • DispOrder

3)MISParticularsMaster:

  • MisParticularsID,
  • MisSubGrpID,
  • SunAcctCode,
  • MisParticularDetail

4)MISBasicTransaction:

  • TransID,
  • SunAcctCode,
  • AccountingPeriod,
  • TransactionAmt

And here is my code as follows:

LIB CONNECT TO 'TempBI ()';

LOAD MISGrpID,

    MISGrpName,

    DispOrder as GrpDispOrder;

SQL SELECT MISGrpID,

    MISGrpName,

    DispOrder

FROM "WSG_BI".dbo.MISGrpMaster;

LIB CONNECT TO 'TempBI ()';

LOAD MISSubGrpID,

    MISGrpID,

    MISSubGrpName,

    DispOrder as SubGrpDispOrder;

SQL SELECT MISSubGrpID,

    MISGrpID,

    MISSubGrpName,

    DispOrder

FROM "WSG_BI".dbo.MISSubGrpMaster;

LIB CONNECT TO 'TempBI ()';

LOAD MisParticularsID,

    MisSubGrpID,

    SunAcctCode,

    MisParticularDetail;

SQL SELECT MisParticularsID,

    MisSubGrpID,

    SunAcctCode,

    MisParticularDetail

FROM "WSG_BI".dbo.MISParticularsMaster;

LIB CONNECT TO 'TempBI ()';

LOAD TransID,

    SunAcctCode,

    AccountingPeriod,

    left(AccountingPeriod,4) as APYear,

    right(AccountingPeriod,3) as APMonthId,

    TransactionAmt;

SQL SELECT TransID,

    SunAcctCode,

    AccountingPeriod,

    TransactionAmt

FROM "WSG_BI".dbo.MISBasicTransaction;

Month_Name:

Load  *  inline [ APMonthId,APMonthName,Monthorder

  001,Apr,1

            002,May,2

            003,Jun,3

            004,Jul,4

            005,Aug,5

            006,Sep,6

            007,Oct,7

            008,Nov,8

            009,Dec,9

            010,Jan,10

            011,Feb,11

            012,Mar,12

      ];

SO MY QUESTION IS THAT I WANT TO CALCULATE SUM OF TRANSACTION OF THE BELOW ATTACHED IMAGE FIELDS:

op.png

SUM OF TRANSACTION OF ALL THE MISSubGrpName fields where MISSubGrpName is from MISGrpName...

I hope you 'll understand my question..I wanna know how to calculate this query

Thanks in advance.

Regards

1 Reply

Re: Help in finding out formula!

Simply select a MISGrpName value and calculate the results with sum(TransactionAmt)