Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
chrisjad
Contributor III
Contributor III

Set Expression with Date Island

Hi All,

 

 I have multiple tables with a created Date/Calendar island. 

 

In my Date Island script, I have added : 

Load

*,
If( DayNumberOfYear(Date) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD
;

On the front-end of the app, I am trying to create a master item to have the YTD sales:

The formula I have without the year condition is the following: sum({$<IsInYTD = {1}>}[Invoiced in GC])

Now, I don' t  know how to include the date condition which would be [Clearing date]=Date .

I have tried this way it but does not work: sum({$<[Clearing date]=Date , IsInYTD = {1}>}[Invoiced in GC])

I have also tried with this formula: if([Clearing date]=Date,sum({$<IsInYTD = {1}>}[Invoiced in GC])) but then my bar chart gives me an error message "the line chart is not dislpayed because if containes only undefined values".

Any idea how to solve this? 

In my line chart, I am using the year and month field of my Calendar Island.

 

Labels (4)
13 Replies
stevejoyce
Specialist II
Specialist II

I think you'll want to do this...

 

sum({$<[Clearing date]=P({<IsInYTD = {1}>}Date) >}   [Invoiced in GC])

Digvijay_Singh

May be like this - 

sum({$<SomeIDField = {"=([Clearing date]=Date)"} , IsInYTD = {1}>}[Invoiced in GC])

chrisjad
Contributor III
Contributor III
Author

Thanks for your reply Steve. I have tried but it gives me a straight horizontal line at a x value 😕

chrisjad
Contributor III
Contributor III
Author

Thanks for your reply, but does not work. basically gives me the same graph as if I was using the IF formula.

It gives me a graph from January until March only. See picture attached. 

Screenshot 2022-03-29 225843.png

 

 

Digvijay_Singh

Are these dates in the same table?  are they having exactly same format? Do you have timestamp or decimal value in your date? probably a sample app can help to try few things

chrisjad
Contributor III
Contributor III
Author

Attached you will see a sample. 

The clearing date is not in the same table as the [Invoiced in GC]). 

But even If I use a measure from the same table, it gives me the same funny graph 

stevejoyce
Specialist II
Specialist II

Why wouldn't your chart dimensions be the Clearing date field (month and year of)?  

Then sum({$<[Clearing date]=P({<IsInYTD = {1}>}Date) >}   [Invoiced in GC]) would work.

 

 

Digvijay_Singh

Somehow clearning date has to associate with date to make it work. didn't realize both dates are not connected. In my expression 'SomeIDField' I meant with any ID field of your data model. By any chance you can rename Clearing date as Date in the CasePaymentStatistic table? and use Link.PST.SEQ in place of SomeIDfield.

chrisjad
Contributor III
Contributor III
Author

yeah I did try that already but gives me the same error message "contains undefined value

 

thanks for the help though