Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vireshkolagimat
Creator III
Creator 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
Vegar
MVP
MVP

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.

vireshkolagimat
Creator III
Creator III
Author

Hi Vegar,

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

Thanks.

jonathandienst
Partner - Champion III
Partner - Champion III

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
Vegar
MVP
MVP

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.

vireshkolagimat
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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.