QlikSense Date Variable not working

[crystles]

I am trying to create a variable that is a Date pulled from Salesforce.

I will be using this variable in NPrinting to create a cycle and folder structure for my reports.

However, I can not get my variable to show up in my QlikSense app. Since it's not showing up there, I'm not sure it will show up correctly in NPrinting either, so I haven't tried that yet. I wanted to make sure it is working in QlikSense first...

The script logic I'm using seems correct, but it is not giving me any results when I place it in a table or text box.

Script Variable

Set vEffectiveDate = 'Max(Date#(Date(Floor(Effective_Date__c))))';

I have tried using the $ () in the QS app and without, but it still will not work.

Any help would be greatly appreciated!

[petter]

What do you mean when you say "I can not get my variable to show up in Qlik Sense"? Don't you see the variable at all? It is a text variable and it would be easy to see if it exists at all and what the content is? Or doesn't it calculate to anything?

The way you have put the expression in the vEffectiveDate you have it as a text string - and it will not calculate anything until you force it to calculate in the QS App in an expression in a chart. That is as it should be.

However to get it to calculate you might have to use the = sign like this =$(vEffectiveDate)).

You expression could be this:

     'Date(Max(Floor(Effective_Date__c)))'    

If Effective_Date__c is a real date, if not make sure that it is when you bring it in in a load statement:

LOAD

    ....

    Date(Date#(Effective_Date__c,'MM/DD/YYYY')) AS Effective_Date__c,

   ....

[crystles]

I have used the formula =$(vEffectiveDate) as a dimension and as a measure in a table and as text in a text box.

Any way I try to use the variable, it just gives me "null", nothing shows up.

[petter]

You can't make this expression in your variable to a dimension as you have specified it since you are using an aggregation function ... Max().

Do you have a screenshot or more to share? In the chart/table that you use it as a dimension and measure what kind of table is it and does the table have other dimensions?

Do you get any kind of values in the three places? A 0 or a dash or blank?

[crystles]

Yea, I know it won't work as a dimension, I was just trying everything to see what it would do.

Script is this

Set vEffectiveDate = 'Date#(Date(Floor([EffectiveDateVariable])))';

I have one other value in the table and in the data model both fields are in the same data model table, so there should be a one to one relationship.

   

[petter]

As a measure it should have an aggregation like for example Max(). There might be more than one date value associated with the dimension you have selected. If it is then Qlik Sense will not be able to know what to do with them unless you have an aggregation function....