Can I use a date formula with secondary dimensionality in my expression?

[#1]

I have a horizontal expression that sums across the rows for a Total number and then within that same Total column, I have another formula for the % of the "Joe and John's" divided by the Total.  The below formula works great to accomplish this. 

What I would like to do is add a date function to this formula ...  I want to be able to manipulate this date function so that I can get today's or yesterday's data. I'm just not sure where the date part goes in the below:

=

If(SecondaryDimensionality()=0,

Sum(IncludeComp) & ' (' &

Num(Sum({<Carrier = {'Joe','John'}>}IncludeComp) /Sum( IncludeComp),'#.00%','.',',')

& ')',

Sum(IncludeComp)

)

[sunny_talwar · Accepted solution]

May be like this for Today():

=If(SecondaryDimensionality()=0,

Sum({<DateField = {"$(=Date(Today(), 'DateFieldFormatHere'))"}>} IncludeComp) & ' (' &

Num(Sum({<Carrier = {'Joe','John'}, DateField = {"$(=Date(Today(), 'DateFieldFormatHere'))"}>}IncludeComp) /Sum({<DateField = {"$(=Date(Today(), 'DateFieldFormatHere'))"}>} IncludeComp),'#.00%','.',',')

& ')', Sum({<DateField = {"$(=Date(Today(), 'DateFieldFormatHere'))"}>} IncludeComp))

[#3]

The formula above didn't work, so I modified it to this:

Both instances resulted in 0 results. 

The first table shows the table I'm looking for, the second table is a result of both formulas.

My expression is actually a horizontal dimension ...

[sunny_talwar]

Can you show your CreateDate looks like? is it read as date by QlikView or not?

Why don’t my dates work?

Get the Dates Right

Once you make sure CreateDate is truly a date field, you can look into the blog to see how dates work within set analysis Dates in Set Analysis

[#5]

My date field (CreateDate) looks like the integer 42,620

[sunny_talwar]

Try this then:

=If(SecondaryDimensionality()=0,

Sum({<CreateDate= {"$(=Num(Today()))"}>} IncludeComp) & ' (' &

Num(Sum({<Carrier = {'Joe','John'}, CreateDate= {"$(=Num(Today()))"}>}IncludeComp) /Sum({<CreateDate= {"$(=Num(Today()))"}>} IncludeComp),'#.00%','.',',')

& ')', Sum({<CreateDate= {"$(=Num(Today()))"}>} IncludeComp))

[#7]

Hmmm, this didn't work either.  The result is the same as before (see the second table in above post).  Any other ideas?

[sunny_talwar]

Can you send me a screenshot of create date in a list box object?

[#9]

[#10]

Sorry to have used up so much of your time ... once I posed the CreateDate, I realized it was an error on my part in not using the correct date format.  I made the datefieldformat = MM/DD/YYYY hh:mm:ss tt and it works.

Thank you Sunny T for your expertise, it is much appreciated.