topic Set expression with variable syntax question or a small issue in App Development

Set expression with variable syntax question or a small issue

Anonymous — Fri, 31 Mar 2017 08:22:56 GMT

Hi,

I have inherited one app with a expression syntax that I do not know how to handle.

It is a WTD calculation and works fine this is the expression :

Sum( $(v_Filter_WTD) Sales )/1000000

This is the expression in the variable

{$<DateKey={"$(='>=' & '$(v_StartOfWeek)' & '<=' & '$(v_EndOfWeek)')"},Year=,Month=,Week=,Day=,fYear=>}

So in the calculation I need to add an filter, so I want to add for example StoreNo = 55 into the expression.

I have not managed todo so.

Any hints?

Message was edited by: Birkir Bjornsson

Re: Set expression with variable syntax question or a small issue

sushil353 — Fri, 31 Mar 2017 08:28:20 GMT

Try this:

{$<DateKey={"$(='>=' & '$(v_StartOfWeek)' & '<=' & '$(v_EndOfWeek)')"},StoreNo={'55'},Year=,Month=,Week=,Day=,fYear=>}

HTH

Sushil

Re: Set expression with variable syntax question or a small issue

Anonymous — Fri, 31 Mar 2017 09:20:05 GMT

Hi Sushil,

Thanks for your valid input, yes this solves this. But I was hoping to be able to have the WTD filter universal and add the StoreNo = 55

into this expression

Sum( $(v_Filter_WTD) Sales )/1000000, that I have not been able to solve.

Any ideas?

Re: Set expression with variable syntax question or a small issue

vinieme12 — Fri, 31 Mar 2017 10:07:01 GMT

please post a sample app

Re: Set expression with variable syntax question or a small issue

sunny_talwar — Fri, 31 Mar 2017 10:48:06 GMT

May be this:

Sum({$<DateKey={"$(='>=' & '$(v_StartOfWeek)' & '<=' & '$(v_EndOfWeek)')"},StoreNo={'55'},Year=,Month=,Week=,Day=,fYear=>}

Aggr(

Sum( $(v_Filter_WTD) Sales )/1000000

, YourChartDimensionsHere)

Re: Set expression with variable syntax question or a small issue

Anonymous — Fri, 31 Mar 2017 15:37:10 GMT

Hi all,

Just added an sample app.

Re: Set expression with variable syntax question or a small issue

sunny_talwar — Fri, 31 Mar 2017 15:39:33 GMT

What is the expected output here?

Re: Set expression with variable syntax question or a small issue

Anonymous — Fri, 31 Mar 2017 15:47:26 GMT

Well the idea here would bet do add an storeno filter into the expression in the top KPI,

So for example set StoreNo = 24 should show 15.08K in the Kpi as WTD sales.

So is it somehow possible to add storeno into the set analysis using the wtd variable as is, So that could be an universal variable and then add whatever extra filters you need into the set analysis.

does that makes sense?

Re: Set expression with variable syntax question or a small issue

sunny_talwar — Fri, 31 Mar 2017 15:54:09 GMT

May be like this?

Sum({$<DateKey = {"$(='>=' & '$(v_StartOfWeek)' & '<=' & '$(v_EndOfWeek)')"}, StoreNo ={'24'}, Year=, Month=, Week=, Day=, fYear=>} Aggr(Sum( $(v_Filter_WTD) Sales )/1000000, DateKey))

or if you can define your variables like this (without the curly brackets at the start and end)

$<DateKey = {"$(='>=' & '$(v_StartOfWeek)' & '<=' & '$(v_EndOfWeek)')"}, StoreNo ={'24'}, Year=, Month=, Week=, Day=, fYear=>

then this might work

Sum({$(v_Filter_WTD)*<StoreNo ={'24'}>} Sales)/1000000

Re: Set expression with variable syntax question or a small issue

sunny_talwar — Fri, 31 Mar 2017 15:55:34 GMT

Tested the second one with a new variable called vTest

Re: Set expression with variable syntax question or a small issue

Anonymous — Fri, 31 Mar 2017 16:02:32 GMT

Yes this works

But this means I will have to create a variable for every storeno I want to filter on.

Is it possible to use the original wtd filter and add to this syntax a storeno filter

Sum( $(v_Filter_WTD), StoreNo = 24) Sales ) as a Pseudo.

Is this possible?

Re: Set expression with variable syntax question or a small issue

sunny_talwar — Fri, 31 Mar 2017 16:05:47 GMT

This is exactly what I gave you

Sum({$(vTest)*<StoreNo ={'24'}>} Sales)/1000000

Where vTest is

$<DateKey = {"$(='>=' & '$(v_StartOfWeek)' & '<=' & '$(v_EndOfWeek)')"}, StoreNo ={'24'}, Year=, Month=, Week=, Day=, fYear=>

Just need to remove { and } from start and end

Re: Set expression with variable syntax question or a small issue

Anonymous — Fri, 31 Mar 2017 16:07:19 GMT

Yes but that means for storeno 5 you would create a new vTest variable?

Re: Set expression with variable syntax question or a small issue

sunny_talwar — Fri, 31 Mar 2017 16:13:45 GMT

That is my bad....

vTest needs to be without storeNo field in it

$<DateKey = {"$(='>=' & '$(v_StartOfWeek)' & '<=' & '$(v_EndOfWeek)')"}, Year=, Month=, Week=, Day=, fYear=>

Re: Set expression with variable syntax question or a small issue

sunny_talwar — Fri, 31 Mar 2017 16:14:50 GMT

Attaching the updated sample (sorry about that)

Re: Set expression with variable syntax question or a small issue

Anonymous — Fri, 31 Mar 2017 16:17:26 GMT

Hey Sunny

You are a genious this works, thank you so much for your help and the effort.

Regards

Birkir