help creating set analysis from if

[ellyodelly]

I have an expression:

IF ($(vDaysDiff) < 31,Sum(If(Date(Num([AR Transaction Date], '#####'))<=Date(varAgeCutoff),[AR Balance],0)))

being somewhat new to set analysis I broke it done..  and cleaned it up a bit- decided I should do the date check in number correct?

I have

vDaysDiff variable defined as

          Num(Date(varSetRunDate ,'DD-MM-YYYY')) - Num(Date([AR Transaction Date] ,'DD-MM-YYYY'))

varSetrunDate and varAgeCutoff are from an input boxes

try part 1

sum( {$< vDaysDiff ={"< 31"}>} [AR Balance])    this works

try part 2

sum( {$< num([AR Transaction Date]) ={"<=  num($varAgeCutoff}" >} [AR Balance])      I just get the - indicating an issue- 

The IF statements are working fine as long as I have the transaction date as a dimension.  I need to remove it to get a higher level of totals- and once I do that I lose any expression that references the date.  In looking at help one suggestion was to use a set analysis.  Is this the right path?  If so - what am I missing?  naturally this is the most basic of the expressions-  they do get more complicated so can I in one expression have a setAnalysis1 + setAnalysis2 + setAnalysis3?

Thanks in advance for some suggestions.

[#2]

Can you attach a sample app for debugging?

[evan_kurowski]

Hello Elly

Elly Odell wrote:

I have an expression:

IF ($(vDaysDiff) < 31,Sum(If(Date(Num([AR Transaction Date], '#####'))<=Date(varAgeCutoff),[AR Balance],0)))

vDaysDiff variable defined as

          Num(Date(varSetRunDate ,'DD-MM-YYYY')) - Num(Date([AR Transaction Date] ,'DD-MM-YYYY'))

varSetrunDate and varAgeCutoff are from an input boxes

try part 1

sum( {$< vDaysDiff ={"< 31"}>} [AR Balance])    this works

try part 2

sum( {$< num([AR Transaction Date]) ={"<=  num($varAgeCutoff}" >} [AR Balance])      I just get the - indicating an issue- 

Thanks in advance for some suggestions.

I think you may have some issues here with the left side arguments of your set-analysis segments. The first argument in a set-analysis segment should refer to a field name from your data model.

In "try part 1", I have a feeling the number being returned is the complete sum([AR Balance]) and the set-analysis isn't producing any filtration because the variable vDaysDiff is not returning a field name.  You can't use set-analysis like an SQL HAVING clause, where you evaluate a calculation result per row and decide if that row makes the selection.  Set-analysis segments are closer to resembling nested subqueries driven by WHERE clauses.

In this case, if you want to include only rows where the difference between the RunDate and the Transaction Date are less than 31 days it might look like this:

Sum( {<[AR Transaction DateNum]={">= Num($(varSetrunDate)) - 31"}>} [AR Balance])

To paraphrase it:  You have a numeric field called [AR Transaction DateNum] which marks your [AR Balance] data with its numeric calendar equivalent.  In set-analysis, you are taking the most recent run date, subtracting 31 days from that point and using that point in time as your filtration criteria.  When you have a row in [AR Balance] where [AR Transaction DateNum] is >= your last run date minus 31 days, that row gets included in calculation
.

"try part 2" may suffer from the same issues.  The left side of the set-analysis segment is "Num([AR Transaction Date])" and I don't think you can use conversion function on the field side (left) argument within set-analysis.  The field name has to be referenced without any wrapping functions.

If you had a purely numeric representation of your transaction dates in a field called [AR Transaction DateNum] and the variable $(varAgeCutoff) were a date integer you could use the following syntax:

Sum( {<[AR Transaction DateNum]={"<= $(varAgeCutoff) "}>} [AR Balance])

The last tricky part here may be converting what has been input into the variable varAgeCutoff and does that entry need to be converted to a valid date number.

For example, if the end user types in "08/07/2014" into an Input box and assigns that value to varAgeCutoff, it is probably being recognized as an incoming string, you need to convert it to an integer before it can be plugged into the set-analysis.  To arrive at an integer you might have to pass through 3 data type transformations:

end-user types in 08/07/2014 into Input field

convert from string ~> to date ~> to number.
Num(Date#('$(varAgeCutoff)','MM/DD/YYYY'))

Sum({<[AR Transaction DateNum]={"<= $(=Num(Date#('$(varAgeCutoff)','MM/DD/YYYY')))"}>} [AR Balance])

[#4]

Hi Elly

Can you please add some more information and sample so that i can figure out what exact issue in your code structure.

Thanks

[ellyodelly]

thanks everyone for the feedback-  I will post a sample as soon as I can catch a break-

Evan- wow thanks for the time spent on this reply-  I do know that the try Part 1 where you suspect is the complete sum- I know it is not.  I have the complete sum in a column right next to this one I am trying to build and I know what values should be appearing for the <31 days.

I did get some help from another source and this statement gave results I was looking for

sum({$<[AR Transaction Date] = {"<=$(=DATE(varAgeCutoff))"}>} [AR Balance])

BUT  when I tried to change the date to another field it came up 0

could this be how dates are loaded?

if I look at the document setting table  - both date fields appear to be the same, integer timestamp date...

however I will try your suggestions and report back.

once again I appreciate the help!

[evan_kurowski]

Elly Odell wrote:

vDaysDiff variable defined as

          Num(Date(varSetRunDate ,'DD-MM-YYYY')) - Num(Date([AR Transaction Date] ,'DD-MM-YYYY'))

try part 1

sum( {$< vDaysDiff ={"< 31"}>} [AR Balance])    this works

My apologies Elly, I'm still somewhat skeptical about the set-analysis in this example, maybe it is the perception that it is working.

The segment above would reduce your sum only under the unusual circumstances that the value of the vDaysDiff expression matched a field name in your data model (i.e. field names that were numbers).  Otherwise I would expect the set-analysis to be ignored and not impact the calculation.  Maybe the differences in results can be attributed to selections in other fields that you are not aware were applied?

This example illustrates:

[ellyodelly]

Right you are- it was perception- on some it was ok- once I ran for a larger set- no so…

I am working on getting a sample to attach to my question.

Thank you.

_Elly

[ellyodelly]

I apologize for the delay- I am attaching a sample.

There are two customers- one with only current and one that ages out. You can see why with initial testing it appeared to be working.

**do not run both customers at once- it isn’t set up for performance just visibility-

Evan- I will download your post now.

Thanks again.

[evan_kurowski]

Hello Elly,

I took a copy of your posted application and converted the 3 columns from IF statements into Set-Analysis and they seem to be working in the straight table (columns 1,2,3 match 5,6,7).

Also, I tweaked your [AR Transaction Date] dimension so that when you make selections in that field, the straight table reduces to just the dates in selection (otherwise set-analysis involving the field [AR Transaction Date] would overrule current selections and generated a row for every date)

These approaches both seem to work though, and unless there's some big performance differential, it is probably nice to have two sets of calculation methodology that can cross-verify.

[ellyodelly]

Evan- thank you so much for the input. I am in a training session – unfortunately not Qlik- for two days - I will sneak this in when I can and get back to you asap

_Elly