Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

vireshkolagimat
Contributor III

Getting wrong Qty

Hi All, In the attached screenshot, i'm using the below expression to get the purchase and sales qty.

 

Purchase Qty - Sum({<DateKey={">=$(=max({<StockTransType={'STKTKEFULL'},
TransNo={$(=MaxString({<StockTransType={'STKTKEFULL'}>}TransNo))}>}DateKey))"}>}GRNQty)

Sales Qty - Sum({<DateKey={">=$(=max({<StockTransType={'STKTKEFULL'}, TransNo={$(=MaxString({<StockTransType={'STKTKEFULL'}>}TransNo))}>}DateKey))"}, InvType={'I'}>}SalesQty)

In the first image the data looks fine but if i multiply the same expression with packsize like below, purchase qty looks good but sales qty showing different number.

Purchase Qty - Sum({<DateKey={">=$(=max({<StockTransType={'STKTKEFULL'},
TransNo={$(=MaxString({<StockTransType={'STKTKEFULL'}>}TransNo))}>}DateKey))"}>}GRNQty*Packsize)

Sales Qty - Sum({<DateKey={">=$(=max({<StockTransType={'STKTKEFULL'}, TransNo={$(=MaxString({<StockTransType={'STKTKEFULL'}>}TransNo))}>}DateKey))"}, InvType={'I'}>}SalesQty*Packsize)

Any idea what is wrong in my expression .

Thanks,

Viresh

 

6 Replies
Partner
Partner

Re: Getting wrong Qty

Is SalesQty and Packsize located in the same table or do they reside in diffrent tables?

My best tip is to add some more dimensions to your table, e.g. do you have transactions IDs or something similar? This ofte helps to understand what's happening.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
vireshkolagimat
Contributor III

Re: Getting wrong Qty

Hi Vegar,

Packsize is coming from products table and sales qty is from transaction table.

Thanks.

MVP
MVP

Re: Getting wrong Qty

Then it depends on how the tables are associated. Possibly there are some values present for SalesQty that are not associated with any packsize, or are associated with more than one possible value of packsize.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Re: Getting wrong Qty

Then I would try to create a chart containing some kind of  transaction ID, your troubled measure and a measure count(ProductID) and/or count(ProductID).

This object will help you identify if you have duplicate or missing matches between the table. In general it's easier to troubleshoot when you have narrowed down your trouble to a limited data set.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
vireshkolagimat
Contributor III

Re: Getting wrong Qty

Hi, It was giving qty twice where ever the packsize is one. I modified the exp like below and i am able to get the qty correctly but total not showing correctly.

= If(Packsize=1,
Sum({<DateKey={">=$(=max({<StockTransType={'STKTKEFULL'}, TransNo={$(=MaxString({<StockTransType={'STKTKEFULL'}>}TransNo))}>}DateKey))"}, InvType={'I'}>}SalesQty),
Sum({<DateKey={">=$(=max({<StockTransType={'STKTKEFULL'}, TransNo={$(=MaxString({<StockTransType={'STKTKEFULL'}>}TransNo))}>}DateKey))"}, InvType={'I'}>}SalesQty*Packsize))

any ways i can alter the above expression? may be aggr fucntion will help?

Regards,

Viresh

vireshkolagimat
Contributor III

Re: Getting wrong Qty

Hi, I tried to add the invoice no and i see that for some the invoices, the salesqty is appearing twice, thrice and even 4 times of the salesqty.