Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I'm not totally new to Qlik - but I haven't used it in a few years. I have inherited a Qlik Sense dashboard with a sophisticated data model and been asked to make some changes. The data model uses a canonical date table and a date bridge to allow the use of a variable - which is sensible and make sense to me. However, where I am struggling is to make a set expression use the variable AND another filter on an element. For example, in the date bridge table i have data as follows:
Key | DateType | Date | COUNTER |
1 | Created | 2020-01-25 | 1 |
2 | Created | 2020-01-25 | 1 |
3 | Created | 2020-01-25 | 1 |
4 | Approved | 2020-01-25 | 1 |
5 | Closed | 2020-01-25 | 1 |
What i'm struggling with is to get the count of rows where the date is selected as a variable, but also only count those of a specific DateType. I tried count({<[DateType]=('Created'),$(vDate)>}[COUNTER]), but this seems to be giving me spurious results. Can anyone help with where i am going wrong?
Just to close this off - the underlying data model was actually broken and the issue wasn't with the expression at all.
I see an syntax error in you DateType modifier. It should be [DateType]={'Created'}
Another error source could be the $(vDate), but it is hard to know as you are not sharing the content of your vDate variable.
Thank you for getting back to me so quickly.
Forgive the typo on the syntax - that was just me transposing it.
The variable is as follows (this I have inherited and not written myself):
=
//Yesterday
if([Calendar] = 'Yesterday', (vYesterday),
//Current Week
if([Calendar] = 'Current Week' , (vCWeek),
//Current Month
if([Calendar] = 'Current Month' , (vCMonth),
//Current YTD
if([Calendar] = 'YTD' , (vYTDCal),
//Last 12 Months
if([Calendar] = 'Last 12 Months' , (vL12Months),
if(GetSelectedCount(Calendar) <='0',(vSelections),)
)))))
from @Vegar 's post :
maye be :
=count({<[DateType]={'Created'},Date={'$(=vDate)'}>} [COUNTER])
vDate must be on 'YYYY-MM-DD' format, you can use
vDate =Date#(............,'YYYY-MM-DD')
for example if the load script :
LOAD Date#(Date) as Date,COUNTER,DateType,Key INLINE [
Key, DateType, Date, COUNTER
1, Created, 2020-01-25, 1
2, Created, 2020-01-25, 1
3, Created, 2020-01-26, 1
4, Approved, 2020-01-25, 1
5, Closed, 2020-01-25, 1
];
output :
Hi,
Thanks for your input - the suggested change didn't help, but now that I look, the date format in the bridge table is actually DD/MM/YYYY format. As far as i can see that format has been used throughout all of the tables.
Can i change the variable to account for that, or does it need to be changed in the load script?
Thanks.
if I understood correctly
Date#(Date,''YYYY-MM-DD'') as Date
Just to close this off - the underlying data model was actually broken and the issue wasn't with the expression at all.