Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
crystles
Contributor III

Total YTD Sales filtered by dimension

I have a straight table with Dimensions, Company, Commodity, Item, Item Description.

I then have 4 expressions

Item Qty = Qty bought by Company on row

YTD Sales = Sum of Sales by Company on row

Total Item Qty = Total Qty of Items bought by all Customers

Total YTD Sales = Total Sum of Sales for CFY bought by all Customers

I can get all of these formulas to work correctly, the only problem is, when I select a Customer, it filters the 'Total YTD Sales'. But If I put a '1' in the formula (set analysis) to keep it from changing, then it doesn't filter the table for the Customer, it will still show all Customers in the table, even though the sum is correct.

Here is my formula for the Total YTD Sales:

Sum( {<INVC_FYADJUST={0} >} TOTAL <CUSTOMER_ID, ITEM> SHIP_ORDER_AMT)

(the INVC_FYAdjust is a field built by the calendar to show the current fiscal year for 0, prior year is 1, and so on)

This formula works if I select an Item or have nothing selected. But if I try to select a Customer, it filters the sum (Total YTD Sales) by the customer (and the table). But I only want it to filter the table to show all items for that Customer, but still have the Sum for this field show the Sum for ALL customers.

Any help would be greatly appreciated. Thanks!

6 Replies
sinanozdemir
Valued Contributor III

Re: Total YTD Sales filtered by dimension

Maybe this:

Sum( {<INVC_FYADJUST={0}, CUSTOMER_ID= >} TOTAL <CUSTOMER_ID, ITEM> SHIP_ORDER_AMT)

crystles
Contributor III

Re: Total YTD Sales filtered by dimension

Thanks for the reply.

I have tried that formula, but it gives me the same result as the '1' in set analysis. It will keep the correct sum in the formula, but it wont filter the table to show only that Customer that is selected.

MVP
MVP

Re: Total YTD Sales filtered by dimension

I think for Total Sum of Sales for CFY bought by all Customers

Sum( {<INVC_FYADJUST={0} >} TOTAL SHIP_ORDER_AMT)


and for ignoring selection of CUSTOMER_ID, use below


Sum( {<INVC_FYADJUST={0},CUSTOMER_ID >} TOTAL SHIP_ORDER_AMT)


Are you sure you need <CUSTOMER_ID, ITEM>

crystles
Contributor III

Re: Total YTD Sales filtered by dimension

Sum( {<INVC_FYADJUST={0} >} TOTAL SHIP_ORDER_AMT)

if I use this formula it only gives me the entire total for everything for every row, that is why I have the <CUSTOMER_ID, ITEM> after the TOTAL

sinanozdemir
Valued Contributor III

Re: Total YTD Sales filtered by dimension

Do you think you can share some sample data or your app?

Thanks

MVP
MVP

Re: Total YTD Sales filtered by dimension

If you don't need entire total, what total you want?

Community Browser