Ignore date selection (dimension field)

[#1]

Hi,

I need the below expression to ignore my selection in dimension field Date

Cannot get it to work properly for some reason, any suggestions?

//O

=If(sum(SoD_Pmt_amount) <> 0,

If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),4) = 0,
If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),3) = 0,
If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),2) = 0,

If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1) = 0,

Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),5),
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),

If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),2) = 0
and
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1) = 0,

Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),3),
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1))))

[sunny_talwar · Accepted solution]

This is what you can do. Create a variable and call it anything you want. I called it vRange

vRange =

='>=' & If(Sum({<Date = {'$(=Date(AddMonths(Max(Date(Date)),-1)))'}, Pmt_Subtype = {'Credit line connected payment'}, [Year-Month] = >} Pmt_amount) = 0,

If(Sum({<Date = {'$(=Date(AddMonths(Max(Date(Date)),-1)-1))'}, Pmt_Subtype = {'Credit line connected payment'}, [Year-Month] = >} Pmt_amount) = 0,

If(Sum({<Date = {'$(=Date(AddMonths(Max(Date(Date)),-1)-2))'}, Pmt_Subtype = {'Credit line connected payment'}, [Year-Month] = >} Pmt_amount) = 0,

If(Sum({<Date = {'$(=Date(AddMonths(Max(Date(Date)),-1)-3))'}, Pmt_Subtype = {'Credit line connected payment'}, [Year-Month] = >} Pmt_amount) = 0,

Date(AddMonths(Max(Date(Date)),-1)-4), Date(AddMonths(Max(Date(Date)),-1)-3)), Date(AddMonths(Max(Date(Date)),-1)-2)), Date(AddMonths(Max(Date(Date)),-1)-1)),

Date(AddMonths(Max(Date(Date)),-1))) &

'<=' & Max(Date(Date))

And then use these two expressions:

Sum Date

=Sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount))

Sum Previous Date

=If(Sum({<Date = {'$(vRange)'} , [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)) <> 0, If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),4) = 0,

If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3) = 0, If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0,

If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0, Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),5),

Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)),

Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0 and

Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0,

Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3), Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1))))

Output:

HTH

Best,

S

[sunny_talwar]

Personally I would have liked to have another month of data to test if this is doing as you would want to do. If you can try and test it with January's data in it.

Best,

S

[#4]

I will try with some more months,

meanwhile i need the first statement to be covering all payments, not just credit line connected payment like the below: I managed to change it myself before but now it does not worrk.

Current:

=If(Sum({<Date = {'$(vRange)'} , [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)) <> 0

Should be something with:

IF(Pmt_amount)<>0 ONLY

This beacuse  some days there are no credit line connected payments.

[#5]

never mind, I think I've fixwed it

[sunny_talwar]

Great,

I got busy with stuff and was not able to respond. I am glad you got it all figured out.

Best,

S

[sunny_talwar]

If everything is completed, I would suggest marking one of the answers as correct (If I have not provided the final solution, than put the final solution and mark it as correct), so that people can use it in the future.

Best,

S

[#8]

Hi,

actually, now that I'm trying with the jan data I can see it cannot handle the new year holidays, any ideas why?

Pls see the attached example file.

best regards,

Olle

[sunny_talwar]

Let me take a peek at it.

[sunny_talwar]

Like I mentioned in your other post on the community that the expression may not work when the holidays are 1 or 3. I have fixed that now. Upto 4 holidays, the expression will be able to handle all 0's. More than that, you will need a bigger if condition.

Not posting the expression here, because it is a huge one. Take a look at the application instead.

HTH

Best,

S

[#11]

Thanks sunindia!

Just realized one problem thuough.

As I think i mentioned earlier, some regular days there are not "credit line connected payments".

These should be showing like 0.

I tried to change the expression so that the "master" IF-statement would test Pmt_amount<>0 rather than

payment_amounts that are also pmt_subtyp = credit line connected payments.

But it does not work for me properly.

I created a new topic, can you pls have a look?

http://community.qlik.com/thread/153856

Thanks in advance,

Olle