Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
john_delligatti
Luminary
Luminary

Variable date Syntax in Set analysis

Hi All,

I'm looking to create metrics based on transactional records of total spend, total number of PO's etc. We're looking to measure how trends increase from month to month since our program is ramping up, it will help give us insight into the upcoming demand.

 

Currently, if I hard code the dates the formulas are working as intended. I'm looking to convert these formulas over to variables so they always represent the trailing 1 month, 2 month etc. I have setup variables that represent the dates I want, yet when I try to incorporate them into set analysis the formula does not work. Any assistance would be greatly appreciated!

Formulas that work 

__________________________________

April Spend- aggr(Sum({<[Reporting Month End Date] ={'4/30/2021'}>} Distinct EXT_PRICE_PO), [PO Key])

May Spend- aggr(Sum({<[Reporting Month End Date] ={'5/31/2021'}>} Distinct EXT_PRICE_PO), [PO Key])

% Difference-

aggr(Sum({<[Reporting Month End Date] ={'5/31/2021'}>} Distinct EXT_PRICE_PO), [PO Key])
/
aggr(Sum({<[Reporting Month End Date] ={'4/30/2021'}>} Distinct EXT_PRICE_PO), [PO Key])

$(vStart_Date) = addmonths(Max([Reporting Month End Date]),-1)

$(vEnd_Date) = addmonths(Max([Reporting Month End Date])

What doesn't work - Incorporating the variable into the formula I've tried a few different versions of syntax to no avail, example is:

aggr(Sum({<[Reporting Month End Date] ={"$(vStart_Date)"}>} Distinct EXT_PRICE_PO), [PO Key])

 

Any help would be greatly appreciated! 

 

 

 

 

 

0 Replies