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: 
Not applicable

Set analysis remove results

Dear all,

I currently have a set analysis syntax which is working and also giving me a list of all results. What I need now, is to remove a small selection from this list.

My current syntax is the following:

sum( { $<Month={"=max(Month)"}, Year={"=max(Year)"} > } MachinePurchaseCost)

+

sum({$<InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>}MachinePurchaseCost)

Now I want to remove some results from the above result. With the following syntax I'm getting the results that I don't want to see.

sum({$<Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>}MachinePurchaseCost)

How can I make the syntax so that it will remove these results?

Anyone have an idea how I can achieve this?

Thanks in advance.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Perhaps this:

sum({ $<Month={"=max(Month)"}, Year={"=max(Year)"}>

         + <InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>

         - <Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>

    } MachinePurchaseCost)


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

Perhaps this:

sum({ $<Month={"=max(Month)"}, Year={"=max(Year)"}>

         + <InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>

         - <Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>

    } MachinePurchaseCost)


talk is cheap, supply exceeds demand
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

=(sum( { $<Month={"=max(Month)"}, Year={"=max(Year)"} > } MachinePurchaseCost)

+

sum({$<InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>}MachinePurchaseCost) ) -

sum({$<Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>}MachinePurchaseCost)

         

Not applicable
Author

Thanks, This one did the trick. My final result looks like

sum({ $<Month={"=max(Month)"}, Year={"=max(Year)"}>


         + <InvoiceMonthYear={"$(=$(vMonthYear))"},MonthYearNum ={">= $(=Max(MonthYearNum))"},Month=,Year=>


         - <Month={"=max(Month)"},Year={"=max(Year)"},DirectShipFK={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}>

   

         - <Month={"=max(Month)"},Year={"=max(Year)"},DirectShipCE={"1"},InvoiceMonthYearNum={"<$(=Max(MonthYearNum))"}> 
} MachinePurchaseCost)

Not applicable
Author

This one I tried myself already but it didn't gave me the correct result. For some reason it always showed a couple of records which should be excluded.